Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql server scripts needed

Posted on 2009-05-18
15
Medium Priority
?
341 Views
Last Modified: 2012-05-07
does any body has script that runs weekly to shrink ldf files
0
Comment
Question by:aatishpatel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
15 Comments
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 1140 total points
ID: 24411548
you have been given the scripts you need to do this in your previous posts.  you have EVERYTHING that you need already.
0
 
LVL 6

Assisted Solution

by:tangchunfeng
tangchunfeng earned 360 total points
ID: 24411569
define a backup job in sql server agent
when backup log file , you have the option to shrink the ldf


0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 1140 total points
ID: 24411584
one of the same links I posted for you before....

http://blogs.techrepublic.com.com/datacenter/?p=448
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 6

Assisted Solution

by:tangchunfeng
tangchunfeng earned 360 total points
ID: 24411622
0
 

Author Comment

by:aatishpatel
ID: 24411709
ok thanx
0
 

Author Comment

by:aatishpatel
ID: 24412342
ok so
I have a problem shrinking the log file. The command that I use is
DBCC SHRINKFILE (IBM_log, 50)
But in result pane it shows:----  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [.        And  in
Message pane it shows :------- [SQLSTATE 01000] (Message 2528)  Cannot shrink file '2' in database 'P002' to 1310720 pages as it only contains 859656 pages. [SQLSTATE 01000] (Message 7993)  Cannot shrink log file 2 (NextGen_Log) because all logical log files are in use.
 And one more thing my log files are stored in L drive L:\logs\. In some databases I see that there is only one file ibm   but in some databases there are two files ld_1  and ld_2. So I do not understand why there are two files. Can somebody explain me what are these two files?  
And I have created weekly shrink log task:- the steps are as below please verify and let me know if its correct.
And they run as sa as a owner and under master database.
USE P001
DBCC SHRINKFILE (NextGen_Log, 51250)
GO

USE P002
DBCC SHRINKFILE (NextGen_Log, 51250)
GO

USE P003
DBCC SHRINKFILE (NextGen_Log, 51250)
GO

USE NGProd
DBCC SHRINKFILE (NextGen_Log, 51250)
GO

Or somebody have some different shrink log idea for weekly basis lt me know please.
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 1140 total points
ID: 24412413
are you doing log backups?
0
 

Author Comment

by:aatishpatel
ID: 24412498
i am doing a full database backup every week and every other day a differential backup
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 1140 total points
ID: 24412574
no log backups?
0
 

Author Comment

by:aatishpatel
ID: 24412691
but full should have that right?
  and one more thing the above steps had problem i did some modifications like just the size that you see is just assumable. but let me know if the rest is ok

USE P001
 
ALTER DATABASE [P001] SET RECOVERY SIMPLE
DBCC SHRINKFILE (NextGen_Log, 10240)
ALTER DATABASE [P001] SET RECOVERY FULL

GO

USE P002
ALTER DATABASE [P002] SET RECOVERY SIMPLE
DBCC SHRINKFILE (NextGen_Log, 10240)
ALTER DATABASE [P002] SET RECOVERY FULL
GO

USE P003
ALTER DATABASE [P003] SET RECOVERY SIMPLE
DBCC SHRINKFILE (NextGen_Log, 10240)
ALTER DATABASE [P003] SET RECOVERY FULL
GO

USE NGProd
ALTER DATABASE [NGProd] SET RECOVERY SIMPLE
DBCC SHRINKFILE (NextGen_Log, 10240)
ALTER DATABASE [NGProd] SET RECOVERY FULL
GO
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 1140 total points
ID: 24412716
>>but full should have that right?

No, you obviously didn't read the articles I gave you.  Just set your databases to Simple (like you're doing in the script) and DO NOT move them back to FULL...there is no reason to.
0
 

Author Comment

by:aatishpatel
ID: 24412927
actually i have to make them full again, because if i had to do simple i would not do shrinkfile. and this is a production database and that is why full recovery is a must for that. now what do you suggest, are the scripts ok.
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 1140 total points
ID: 24412967
the scripts are fine..but being ina  full recovery mode DOESN'T DO ANYTHING unless you're doing log backups....nothing whatsoever.  that is why I suggested you keep it in the simple mode.  The only advantage of FULL is that you can recover to a point in time...but you'll never be able to do that if you don't make log backups.
0
 

Author Comment

by:aatishpatel
ID: 24413251
but my manager said that every sunday evening just do shrinkfile. because on saturday we run full backup. that's all he said.
0
 

Author Closing Comment

by:aatishpatel
ID: 31582572
thanx all
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question