I wrote simple script to check space used by tables:
CREATE TABLE #SpaceUsed(
TableName NVARCHAR(128),
NoOfRows INT,
Reserved NVARCHAR(18),
Data NVARCHAR(18),
Index_Size NVARCHAR(18),
Unused NVARCHAR(18)
)
GO
sp_msforeachtable "INSERT INTO #SpaceUsed EXEC sp_spaceused '?'"
SELECT * FROM #SpaceUsed
SELECT
CAST(Sum(CAST(Replace(Reserved,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalReserved,
CAST(Sum(CAST(Replace(Data,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalData,
CAST(Sum(CAST(Replace(Index_Size,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalIndex_Size,
CAST(Sum(CAST(Replace(Unused,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalUnused
FROM #SpaceUsed
Could some one help me to explain what does it means when I ran sp_spaceused on the database it reported ex. -15430kb for unused. Does this mean that the index ran out off space. Do I need to increase space for the database. I will try to run the dbcc check hope it will fix the problem.
How can I determine how much unused space to remove from database files? I am setting up a maintenance plan and I am trying figure that out. Also, is there a rule on how far to shrink TL?
Hello, I have a table which resides on a diff.filegroup. The space allocated is 7700MB. Now ehen I use sp_spaceused 'table_name' to see how much space is left it's giving me data as rows reserved data index Unused at 1000hrs 629879 6777904 5068344 8 1709552 After an hour 637537 6780336 5070800 8 1709528 After 2hours 643883 6782560 5072904 0 1709656 After 2.5hrs 646887 6783584 5073920 0 KB 1709664 kb after 3hrs 647239 6783712 5074056 0 KB 1709656 K If you note rows are increasing,so is the reserved place and data but unused is also increasing.I think it should come down. What behavior is this?How can I find out how much space is left in this table residing on diff.file group? TIA
On a production database, there is a 2GB database, when I runsp_spaceused it indicates a very high quanity of unused space. Thedatabase has been shrunk & free space sent to the OS. Why is thisvalue so high, what can I do to reclaim the space?database_name database_size unallocated space------------------------------------------------------------------------------DB_00001 2004.13 MB 49.64 MBreserved data index_size unused------------------ ------------------ --------------------------------1531248 KB 412720 KB 165168 KB 953360 KB
I am working with a large database that has its tables stored on a secondary filegroup. I'm trying to shrink the size of the files but I can't seem to get the system to free up the unused space. I've tried shrinkdatabase and shrinkfile both with and without the truncateonly option. Has anyone else had this problem? Is there a workaround? Any help would be greatly appreciated.
I'm trying to figure out how to reclaim unused space in a huge table (tbl) after setting one of the text columns to be the empty string. Other tables have foreign key columns associated with tbl.
After doing something like this,
update tbl set col = ''
the table doesn't automatically reclaim that space. If I do a direct insert into a new table like this,
insert into tbl2 (cola, colb, colc) select * from tbl
the new table is smaller as expected (about half the size in my case). Any ideas?
I have a table which has 6 text columns (tblA).. I no longer require 1 of those text columns and want to reclaim the space that it is currently taking up..
Is the only way to BCP out all the data (except the 1 column i no longer require) drop the column and BCP the data back into the table?
currently stuck with an issue where I need to reclaim the unused table space in SQL server.
ISSUE: Due to database size issue we have moved all the images from database to filesystem and want to reclaim the unused space now....I have tried shrinking the database and rebuild the indexes but didn't see any difference in the table or database size.
Similarly I have identified many Non clustered indexes on big transactional tables(~ 4 million records) that where not used since GO live 1 year back, so I wanted to drop these indexes to cut down the performance and maintenance overhead , so my question is will dropping these indexes reduces the database size?
best method to my issue regarding unused space. SQL SERVER 2014 BI EDITTION..I have a table that showed 62% Data, 7% unallocated, and 29% unused space.I ran the ALTER TABLE <table name> REBUILD, which changed it to 32% data and 67% unused.What I do with this table monthly and what I believe is causing this unused space issue is this:Every 1st of the month I get a csv file that holds around 3.2 million rows/15 columns of data. About 3 gbs every time.It's 13 months of data. I remove the last 12 months from the current table and import the new 13 month data.I do this twice a month and have no choice because the data is constantly updated and why we get it twice a month.So I am deleting around 6 gbs of data a month as well as adding around 6 gbs of data a month.
I believe this is why I end up with so much unused space, but just found the REBUILD command, which worked but now a small hit on performance and have too much space in the current table. I have read several times that shrinking the file is no good, but what other way to get rid of the extra space in this table? Also, the table doesn't have any indexes or primary key because of duplicates.
OS: Windows Server 2008 R2 Standard SQL Server: 2008 SP1 Standard
We have a database with about 500 GB of free disk space on data file and the database is being set to read only mode for the fore-see-able future. We would like to release this unused disk space. We know that we could shrink the data file and then work on re-indexing to remove fragmentation.we wanted to check if some other method like backup and restore of the database could free up unused disk space in the data file. if someone was able to release unused disk space on data file by implementing a backup and restore.
how to claim unused free space at table level. The database size is of 4.6TB, recently I deleted some data which is of almost 1.5TB but my unused space has grown upto 2.5TB leaving me short of space on my drives. How do I claim this usused on to OS Disk space?
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
We've got an alert setup on our production database to warn us when the log file(s) exceeds 7 gigs. The Alert is triggering:
"The SQL Server performance counter 'Log File(s) Size (KB)' (instance 'Lexus') of object 'SQLServer:Databases' is now above the threshold of 7000000.00 (the current value is 7057656.00)."
However, according to the file system, the database properties screen files, and properties tabs, the log files combined are at under 5 gigs so this alert should not be going off.
The scary part is, when going to right-click on the database, choosing "tasks" and going to "Shrink > Files", the "Free Space" shows negative numbers for the first log file:
Log 1 Currently Allocated space: 118.69 MB Available Free Space: -5323.24 MB (-4485%)
Log 2 Currently Allocated space: 4853.13 MB Available Free Space: 411.20 MB (7%)
Has anybody ever run into this? Should I be worried that there is a bigger issue at hand?
Hi, I am trying to do this: UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl') What would be the syntax. Any help appreciated. Thanks
I am generating a Report from Sql Data Source in Sql Server 2005 and viewing the Report in Report viewer control of Visual Studio 2005. The data in the Data Source contains string with multiple spaces (for example €œ Test String €œ) but when they get rendered in Report viewer control, multiple spaces gets converted to single space €? Test String €œ.
I tried following solutions 1) Replacing spaces with €œ €? 2) Inserting <pre> tag before the string and </pre> tag after the string (Also tried <Pre> instead of <pre>)
But in all the cases result is same. The Report Viewer control is showing €œ €? instead of space and €œ<Pre>€? tag instead of preserving spaces.
Please provide me a solution so that spaces can be preserved in Report Viewer.
I am using the below script to get space alerts  and now i am interested in sending alerts  if for any drive space available is Less than 10% or 15%.. how to convert beelow code to find in %Â
Hi.. I was doing a good maintenance on my DB and my trans log LDF keep growing until 30GB but my DB data file MDF is only 2GB. I found the two following method to reduce my log size.
Method 1: I used veritas to backup log file with truncate Method 2: I used the shrink database option in Enterprises manager to shrink it (file chosen=log , use default option)
After doing that, I found my LDF log file is still about the same size=27GB but when I see clearly, from the shrink database windows, the log spaced used reduced to only 100MB, the allocation log space is still 27GB. Why? How to make the LDF smaller to be the around the same size as the space used 100MB?
This is driving me bananas. Can't find any info on this anywhere....SQL 2000 seems to replace double space with a single space when I seta varchar field to " " (2spaces), it only stores " " (1space). Whyon earth would microsoft do this? If I save 2 spaces - I WANT TO SEE2 SPACES!!!!Can anyone help? Is this a database setting? Is this due to usingvarchar?Any help appreciated.Colin Hale
Dear Experts, I'm working for an ERP company, for our product, there are around 2000 tables. in each table, we are using 10 extra columns for our future use. all these are using nvarchar(50) datatype.
almost 95% of the columns in these are not in use almost. those are having null value and in some columns, the values are like 0,1...
is this affects the performance?
please spare your valuble time
thank you very much
Vinod Even you learn 1%, Learn it with 100% confidence.
I would like to verify that we do not have any unused login ids. I know I can turn on auditing for successful login attempta and either manually check the logs or write some type of findstring code to capture successful login attempts and then for each success, remove the id from a master list, but I was wondering if there may be a simpler approach. Any thoughts?
I have a 3rd party developed app which has over 42000 data fields in 1200 tables. It is a heavy transaction type app. Of these fields over 10,000 are null for every record. SOme of their homegrown references these fields (and this can be removed), and in many instances many of the fields are not referenced at all. My opinion is that these extra fields are adding a heavy overhead to updates, inserts etc to the server. Any disagreements?
What I am trying to do is get all of the records in a table that are out ofsequence so I know which account numbers I can reuse. I have a rangeof account numbers from 50100 to 70100. I need to know which accountnumbers are not stored in the table (not currently used) so I can use.For instance say I have the following data in table:Account Name--------- --------50100 Test150105 Test250106 Test4......I should see the results:50101501025010350104because 50101-50104 are available account numbers since not currently intable.Thanks.
I have inherited support for a database with many used tables. Thereisn't any documentation on what is used or dead. I was hoping to runtraces and capture the objectid but the property doesn't work thatway.Any good ideas would be appreciated.
I'm working on an app that contains a series of 100 very compute-intensive queries that altogether take 30 minutes on my laptop machine. The app may be atypical in that there is just this series of expensive queries, and not lots of simultaneous simple queries.
To speed up testing, I bought a Dell PE2900 Quad-Core Xeon with 16GB of RAM and four 15000rpm hard drives. It runs 64-bit Windows 2003 server and 64-bit SQL Server 2005 dev edition.
Unfortunately performance is worse than on my laptop. Task manager reports (almost constant) 13GB of available physical memory, while processor usage hovers around 30%. The page file usage is constant at 3 GB.
I am unsure if i am managing my connections in the correct way... but as it stands we have them in a .dtsConfig file that each package references in order to get the relevant details..
This is working fine, except that in total our ETL project uses 6 different connections, and none of the packages utilise all 6.
This leads to an error message upon opening up EVERY package stating that there are connection strings in the .dtsConfig that are unused, yeah, thats the idea!
The way i have gotten around this, is to just include all 6 connection managers in every package, but my question is, is there a better way to achieve this same result??
are we going about this in a bit of a cackhanded way?
I have an old DATABASE that is not more in use , so I want to drop that database. But before that I want to check that is there any application running on it or not.
There are too many indexes built on DB. As per the naming convention it seems the indexes are built as per the suggestions provided from execution plan. I presume most of the indexes are used only once in a month for the reports but are hampering the performance of daily running queries. These are also occupying a lot of space.
To confirm on this I have used the below query to know & identify the unused indexes. I have recorded the counters before and after the huge operations and I observed NO CHANGE in any of the values.
What the below values exactly indicate and when do they change? Is it good to delete the indexes having low USER_SEEKS, USER_SCANS, USER_LOOKUPS?
Query: SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS,