UPDATE: Please follow link for SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008.
Sometime, it looks impossible to shrink the Truncated Log file. Following code always shrinks the Truncated Log File to minimum size possible.
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
[Update: Please note, there are much more to this subject, read my more recent blogs. This breaks the chain of the logs and in future you will not be able to restore point in time. If you have followed this advise, you are recommended to take full back up right after above query.]
UPDATE: Please follow link for SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008.
Reference : Pinal Dave (http://www.SQLAuthority.com), BOL
314 Comments. Leave new
Ritesh,
The ‘quote’ did not help. Same error. Baffled now !!
————————————–
why don’t you try this one?
USE 8PSTDB
GO
DBCC SHRINKFILE(‘8PSTDB_log’, 1)
BACKUP LOG 8PSTDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(‘8PSTDB_log’, 1)
GO
BTW, it is not a good idea to truncate log manually rather do take backup of your database regularly and also take backup of your log files after every few hours, depends on your risk handling capacity.
how to migrate the jobs?
you can look at following link which might help you.
https://support.microsoft.com/en-us/help/314546/how-to-move-databases-between-computers-that-are-running-sql-server
There is one topic “Step 4: How to move jobs, alerts, and operators” which shows you how to migrate your jobs.
Thanks for this. Helped out quite a bit with some DTS and DB sizes getting large.
I LOVE YOU.
I had a 190Gb transaction log that I could not shrink.
Used your code and SHAZAAAM.
Now I need to set up a maintenance plan so this wont happen again.
Hello Abernut,
What is the recovery model of that database? If the database is not a publisher in transactional replication or log shipping then Changing the db to simple recovery model do all to shrink the log file.
Is this database being replicated or ever configured for replication?
Check for any open transaction using DBCC OPENTRAN.
Try using CHECKPOINT and then BACKUP the transaction log.
Regards,
Pinal Dave
can anyone help or guide me
creating the replication of the database
for sql 2000 / 2005
Hi,
I have a bunch of databases SQL Server 2005) whose logs have been growing too big recently. Two of them refuse to truncate the logs after a scheduled job runs a log backup. I have since run manual backups to disk and with truncate_only option but they will not truncate.
Is there any reason why these will not truncate and how can I force the truncate since we are running out of SAN space?
Sincerely,
Nelson.
Hello to all,
We have SQL 2000 with very big database about 500 GB.
We deleted about 50% of the data, we dropped all indexes
With simple recovery option. (allot of image BOLB fields)
However, the database size increased after delete process, we ran dbcc shrink db
And shrink file, but the size still same.
Any idea to get rid of all empty spaces, and what the best practice to reduce the size to 250 GB?
Note: we don’t have much space to move the database around
Thanks in advance
Linda
Hi there,
I have two queries one pertaining to Sql Transaction log bulky and second regarding to stored procedures.
Sql Transaction log – I have production database with the data file size of 240 mb and log file size of 47 GB.
We have scheduled for a full backup everyday. The database is in the simple recovery mode. Intially when the database was backuped, the .bak was same as data file.
We tired backing up the transactional log, but no luck, and tried with DBCC Shrink no effect, the log file is still the same.
Kindly suggest.
Stored procedures – We have written a stored procedure for reports, which uses number of temp tables for computation.
Now my query is, if the sp is executed by multiple users at same instance or time, will the sp executes, will the output be correct and will the time taken for execution be more.
Is there any possiblity to generate dynamic temp table,
for e.g create table with the session Id.
If there are any suggestion please do suggest.
We need to implement the report with sp, any work around.
Hi Udaya,
In database property check the “Space available”. IF this is near about the size of transaction log then it shoud be shrunk. If this value is small then check whether any transaction is running from long using DBCC OPENTRAN.
It may be that you assigned the 47 GB space to .ldf file at the time of database creation. If that is the case then use DBCC SHRINKFILE and define the free space size.
About temp table: Don’t worry sql server handle the issue you are suspecting and uses the same approach that you are thinking to plan manually.
A temp table (not global temp table) is available only to the session that created it. Multiple user can create temp table with same name at the same time because sql server internally use session id in there name to uniquify them.
Regards,
Pinal Dave
Many Thanks for the reply Mr Pinal.
When the database was created the size mentioned was 2 mb and incremental was 10%.
I have executed DBCC OPENTRAN,
Output – Replicated Transaction Information:
Oldest distributed LSN : (2091:12488:1)
Oldest non-distributed LSN : (2232:124689:1)
Space Available : 16.85 mb
I did shrink the database with the above command
DBCC SHRINKFILE(, 1)
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE(, 1)
GO
but nothing seems to happen, the size is 47 GB.
— Stored procedure —
Sorry, missed to mention, we are using single user id login in the reporting application. If any user access the application same authentication is used.
In this the case, I belive the table would be rewritten, if the second user accesses the report.
Is there any work around?
When the sp is executed by users at the same time, the execution time is more. for instance when executed by a single user it takes 30 – 40 secs, where as when multiple user execute, its around a minute +.
Is this cos the sp is trying to access the same table, its slow.
Regards,
Uday
Hi Udaya,
As the OPENTRAN resturn on process, that means one transaction is running and you need to stop that.
If the data is not replicated then kill that process.
Every time sp would be called the temp table would be created and if you think creating a temp table is taking time then keep a table in database and use it in your SP.
Regards,
Pinal Dave
Hi Pinal,
Sorry for the late reply, I was on vacations.
I went through some of the articles on how to kill a process.
In some article they refer to SPID. How do I get the SPID for the open transaction.
Its a production server and replications are active, the replications are scheduled once in every 4 hrs.
Please let know know if there are any command or script that needs to be executed to kill the process.
–To know the ope transactions
DBCC OPENTRAN(SPID)
–To kill any SPID’s
KILL
This works! just wanna make everyone rest assured:)
Its an handy script for the DBA’s.—-Bobbili Venkateswara Ra0
Hello,
I have one question which i am just able to find answer for.
Suppose I have my .mdf file on D drive and .ldf file on E drive and due to some reason my D drive crash, and all my backup is also on D drive. So now if i want to restore my database in minimum possible time then how will I restore database now that i dont have my mdf file as D drive is not available.
If any one can answer this question then it will be of great help to me…
Thanks in advance.
Hi Pinal,
I deleted all the replication and the replication publisher as wel and recreated the publisher and replications. After doing this, when the backup job was run, the size of .bak was around 260 mb from initial 48 GB.
Regards,
Uday
Hi Sunil,
It is not possible to restore your database if you don’t have MDF as well backup files. only log file is not enough as it doesn’t have any data, it just contain transaction.
I am working in one Data Service Process company. I was deleted 25 lakhs data from Database. After delete process, the mdf file size is increased (Before delete : 90 GB, After Delete : 95 GB). Now i am not considered about log file (ldf).
How i decrease the size of mdf file without data loss ?
Please give me its solution
Thank you