SQL Server 2008 :: Track Unallocated Space On Database?
Apr 1, 2015
I am trying to track unallocated space (because on my database growth is not kicking in) So using below query but not working!!! Is there any way I can track 'database size' and 'unallocated space' on a single database?
I'm having a problem to which I'm sure the answer is simple...
All I want is a list of databases on my server with their allocated size and the free space within. Something similar to the first table that sp_spaceused gives you but on a server wide scale.
As I say, I'm sure there's a simple solution out there, but alas Google has failed me.
I've gone through the forum and have seen several others with a similar situation. I recently noticed my .mdf file grew to 200GB yet the data in the file is only about 40GB. I run sp_spaceused and get the following: database_size = 176GB, unallocated space = 134GB, reserved = 43GB, data = 17GB, index_size = 19GB, unused = 6GB.
I've tried "dbcc shrinkdatabase (mydb, truncateonly)", tried restarting server, tried running a maintenance plan including a reindex of the database, and I don't know what to do next.
At my current workplace, whenever I check table sizes using the'reserved' column from sysindexes, or sp_spaceused, I get a total forall user tables which exceeds the physical size of the database.Running sp_spaceused with no parameteres, I get a NEGATIVE value forunallocated space in the database, but only see this if I return theresults in GRID format in QA (text format gives less output):DatabaseName DatabaseSize Unallocated Spacexxxxx_xxxxx11502.38 MB-4874.80 MBReserved Data index_size unused16602800 KB7013752 KB2381904 KB7207144 KBIf I re-run sp_spaceused with the @updateusage='TRUE' option, thisoutput gets corrected to:DatabaseName DatabaseSize Unallocated Spacexxxxx_xxxxx11502.38 MB2773.76 MBReserved Data index_size unused8770680 KB6928168 KB1808096 KB34416 KBwhich shows a substantial difference in the Reserved/Data/Index/Unusedsizes.This happens every day - any ideas about:1) Why this might be happening on such a large scale, and2) Is it conceivable that these discrepancies in space allocationinformation could be causing performance problems? I can imagine thatif the database is trying to locate free pages on extents yet itsinternal view of these doesn't match reality then this could impededata insertion.Offers anybody?
Hi all, When I am rebuilding the indexes on the tables, I am getting lot of free space( unallocated) on the database.
Before rebuilding the indexes , the size of the database = 385 Gb After rebuilding the indexes, the size jumps to = 572 Gb (i.e.) This means 187 Gb of unallocated space .
The Command use to rebuild indexes is: USE [databasename] GO ALTER INDEX [PK_index] ON [dbo].[tablename] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = OFF ) GO
So, every time we rebuild indexes, we have to shrink the database (or) Is there anything else ,I should be doing. Thanks.
On the drive where the datafiles resides there is enough free space . Also, the datafile and transaction are set to auto grow with unlimited file growth
FYI - the OS is Windows 2000 and the DB is SQL Server 2000 with SP3
I have a specific requirement. I need to insert the DML statements executed from Management Studio into a SQL table. We have SQL Server 2008 R2 and 2012 instances.
I would like to know about the DML process (Insert/update /delete) in a particular table .. it is like change tracking but I also want to know the modification date
I know CDC ( Change data capture ) but unfortunately it needs SQL 2008 developer/enterprise edition and my SQL server is SQL 2008 STANDARD edition.
Hi anyone please help! I have created the database driven web application with asp.net and sql server 2000. now I want keep track three operation(insert, updata and delete) that have been made on tables in a SQL Server 2000 database. what i did is: 1, create a audit table with columns: auditTable, actions, actionUser, actionTime 2, create three trigger(insert, update and delect respectivily) for every table my problem is that i can not get right user name. I use form authentication and i stored user login information in the database. every time, no matter who is logining to the web application, the action user is always SA. I user user-name() function to get userName(actionUser). Please anyone can help me to get current login user name, or tell the best way to track operations on a table. Thanks jili
I am only DBA in my company and client want to know the growth rate of his SQL server DataBase which is in production. How can I get the growth rate per day?
select Computername from dbo.ADAssets where Computername like 'AIRLBEOF3565%'
I get both records,but if I do this
select * from dbo.ADAssets where Computername in ( 'AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d', 'AIRLBEOF3565' )
I only get AIRLBEOF3565
So the big picture is that I need to compare 2 tables to find records that match & don't but that I get matches that shouldn't be & matches that aren't.
This table takes up almost 80% of my database size, and the information is this table just captures the time spent by a user on the website(not very critical data)
I would like to know how to delete the entire index (which is what is occupying most space) to free up disk space. the index is a clustered index.
We are receiving following alerts frequently about 1:40 AM in the morning. We have backups running on 11:00 PM everyday and rebuild job running at 2:00 AM. Not sure the exact cause of this error.
Error:
The file group "PRIMARY" for the database "tempdb" in SQL instance "MSSQLSERVER" on computer "XYZ" is running out of space.
tempdev Initial size : 133,100 MB Growth: By 10 percent, Limited to 140000 MB templog Initial Size : 5,475 MB Growth: By 10 percent, Unlimited
I have a table 300+GB. it holds 10 years of Data. I need to delete 5 years of data and put it to another server so I can have more space.
If I delete 5 years of data, Transaction log gets so huge and size of the database even gets bigger because of the .ldf file which even gets bigger! I think I can shrink the log file and the data file. Is this the best way to do it?
how to handle space between multi-value parameter values in SSRS. For e.g. if the values are as follows -'KLO LUG', 'HGY KIU', 'LOT JUY', I know I can use the split function for the commas but its the space between the value which is the problem.
one of my database data file is 100 GB and the log file is 500 GB.DB is in full recovery model and the transaction logs happen once in 6 hours.Even then, the Database log file isn't reducing in size.
While my application is running on the production server, I want to develop on the test server. After a few weeks, I want to update the application, and have to update the database structure on the production server also with the most recent one from the testserver, but without deleting the current data on the production server.
I create/modify all tables in SQL Server 2005 via Management Studio. The application is built in VS2008 Pro. I'm using SubVersion (SVN).
I can let Management Studio generate scripts for every change and store them manualy, but that's a little bit too much work. What I want is a sort off version control solution. A solution that a service running on the background tracks every change on a specific database on my test server, and stores them as T-SQL code in a repository. Then I can collect all those T-SQL scripts, and run them against the production server.
I have a requirement. I am having a database which is having views,procedures and tables. Many users are having access to the database. If i want to track all the schema changes happening in the database how to achieve that? As in i have used some tables in a procedure and someone might change the column or drop the column. So it is going to have impacts on my procedure. I need a trigger which tracks all the changes done in a database and the impacts those changes are going to make.
I am an advanced user of MS Access and other databases but relatively new to SQL Server. I am utilizing SQL Server Express to analyze a database that was created by another entity. I am interested in looking at any specific record in any table and seeing who created that record, what date/time the record was created, who edited the record, when that edit occurred, etc. I don't see any such fields in the database. Would the designer of the database have to have explicitly added such functionality, or is this information available but just hidden from the novice's view?
I'm actually taking Microsoft's 2779 and just finished a lab where wekept track of our changes to the database.However, I'm not happy with the scripts interface because it does nottell me the chronological order of my changes to the database.Could someone share with me their technique for keeping track ofdatabase changes?I'm actually thinking a set of tables would be best, because sometimesyou want to know what database object you made a change to and othertimes you want to know when you did something...
We have a particular database sat on SQL Server 2012 box along with about 20 other databases.
What I require is a method/Script/Audit that will simply track anyone who logs (successfully / unsuccessful) into this one particular database on the server (The single database is the key as the end user does not want information on any of the other databases that sit on the server), it also has to log time the attempt was made and it must track the logins via SQL Server or the application itself that is attached to the database.
I am using Northwind database to Create a view showing every order that was shipped to Spain. Name the destination column 'DestinationSpain'. Include code that checks if the view already exists. If it does, it should be dropped and re-created.
Here is my script:
use Northwind GO
/*STEP 2, #1*/
/* does it exist, if so drop it */ if exist (select * from dbo.sysobjects where id = object_id(N'[dbo].[OrdersToSpain]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[OrdersToSpain] GO
/* Create the View */ create view "OrdersToSpain" AS SELECT Orders.OrderID AS Order_ID, Orders.CustomerID AS Customer_ID, Orders.OrderDate AS Ordered_Date. Orders.ShippedDate AS Shipped_Date, Orders.ShipCountry AS DestinationSpain FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Orders.ShipCounty LIKE '%SPAIN%' GO
Here are the errors I am getting:
Server: Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'select'. Server: Msg 170, Level 15, State 1, Line 6 Line 6: Incorrect syntax near ')'. Server: Msg 170, Level 15, State 1, Procedure OrdersToSpain, Line 7 Line 7: Incorrect syntax near '.'.
I had to to relocate the database log file and I issued an Alter database command but by mistake I put a space in the file name as below. The space is at the beginning file name. Now I am unable get the database loaded to SQL Server. The database has 2 replications configured, so deleting and re-attaching the database means the replication needs to be re-configured. Is there an alternative way to issue a command to update the database FILENAME ? Not sure if this can be edited in master database (sys files).
ALTER DATABASE [User_DB] MODIFY FILE (NAME = User_DB_log, FILENAME = 'I:SQLLogs User_DB_log.ldf') GO
SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission] FROM [sys].[database_permissions] sec JOIN [sys].[database_principals] prin ON sec.[grantee_principal_id] = prin.[principal_id] WHERE sec.class = 0 ORDER BY [User], [Permission];
but the results are this: 2 columns - User and Permission
User Permission User1 GRANT CONNECT User2 GRANT CONNECT
IS there a way in SQL Server (2005/2008/2012) to run a script against a Database that will show all users that have permissions to that Database and the type of permissions?
What would be the best practice to follow to keep track of MS SQLserver changes... Stroed procs, tables, views, triggers, indexes, DTSand also jobs ect....I am not quite sure how Source safe works with sql server. Any otherway to do this... Even if its manual work, its okey.. I wouldappreciate if any of the DBA's let me know how they are facing thisissue....Thanks in advance...
Hello there,Does anyone know of a way to track changes to an SQL Server database so thatI can easily run those changes at a later date?That is, I want to make schema changes, and record those changes so that Ican execute them 6 months later on a copy of the orignal database.Thank you kindly for any ideas anyone may haveJohn
