Solved

sql server scripts needed

Posted on 2009-05-18
15
331 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 380 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 120 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 380 total points
ID: 24411584
one of the same links I posted for you before....

http://blogs.techrepublic.com.com/datacenter/?p=448
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 6

Assisted Solution

by:tangchunfeng
tangchunfeng earned 120 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 380 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 380 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 380 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 380 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

738 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