Tag Info

New answers tagged

3

There are basically two things to consider: You cause fragmentation when shrinking the database If the database grew yesterday, there's a good chance it will grow again tomorrow. To address 1, you need to defrag indexes once you've shrunk the database. The database will grow when you do this, although probably not back to the original size. To address ...


13

I will be deleting approx 400 million records from this table. Hopefully, you are doing it in chunks - to avoid bloating transaction log. notice that i am not see any drop in my hard drive space You wont, as you have to explicitly shrink the database file to release space. Just deleting the records, SQL server wont release the space back to the ...


5

you have good reason to be concerned as DB shrinks in SQL Server often "suck". Paul Randal, the head of the Storage Engine in SQL 2005 stated, ShrinkDB is written very poorly. It will find empty space by taking the data at the very end and put it in the very beginning and keep doing this until it has free space at the 'end' of the DB files. At this point ...


2

It's ok for you to shrink it enough to ensure your server won't crash in the amount of time it takes you to assess your storage needs and plan hardware / hosting services appropriately (see question 4). No it won't lock it. See restrictions. Shrink the database because it keeps things simple. I suggest contracting the expertise to conduct the study and ...


3

To understand Transaction Log architecture you have few good articles Understanding Logging and recovery in SQL Server Transaction Log physical architecture I would ask you to take some time and read these articles specially the first one. so is there any way I can remove entries from log file for all transaction other than active transaction. ...


0

If possible you should reduce the amount of data changed per transaction. Commit more often with less data. Otherwise increase the log size even more. If you are running out of space, you need to increase.


4

•On my observation, I found that this is happening only with insert statement not with select. So, I presume that log entries will only be for INSERT/UPDATE & not for SELECT statement in any case. Am I correct? Select statement as such is not logged as compared to DML statements like insert , update and delete. If you see output of fn_dblog for ...


0

If your databases are in full recovery mode you will need to backup the transaction logs, when that is done you can shrink the log files and setup a regular log backup to minimize growth and keep good backups. I would reccomend that you read this: https://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx but first things first. The first step is ...



Top 50 recent answers are included