SQL Server Performance Forum – Threads Archive
Shrink Database not working
hi, we have a database having 4 data files placed on 4 different filegroups. In which the primary file size is 20 gb. it has only system tables.we r trying t0 shrink this file but no luck. we used the shrinkfile, shrinkdatabase with truncateonly option, but no luck. please help
What recovery model do you have? Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
it’s bulk logged
How often do you backup log?
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
daily on hourly basis but the shrink problem is with the data file
Check thishttp://www.sql-server-performance.com/absolutenm/templates/?a=260&z=1 reference too. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Crazy question…..is there any free space left? If so, how much is left? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
hi, Ya, it ‘s crazy. but that’s the problem. the free space left on the drive is 138 GB.
this file belongs to the primary filegroup. previously we imported a table of size 15 GB using the "select into" query. then i deleted it, changed the default filegroup and imported the same table again.
but the primary filegroup is still 19 GB. I used shrinkfile, shrinkdb and shrinkdatabase also but no luck.
Run DBCC OPENTRAN to check what kind of open transactions in the log.
Also run DBCC SQLPERF(LOGSPACE) for information. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
I have run both the commands there are no open transactions and the DBCC SQLPERF(LOGSPACE) gave me this result D3_New 1585.4297 92.654053 0
This shows the transaction log has occupied more than 90% of space, run BACKUP LOG to backup the tlog and run the command again. If this doesn’t decrease then I feel some kind open transaction is striking in the log. Also check what kind of jobs are scheduled at this time. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>