Posted by joshua.smith on Feb 8, 2012 in sql server | 0 comments
every once in a while i run into an issue where i have some log file on a microsoft sql server that has not been properly configured and is taking up a hundred gigs.
and inevitably, i end up spending the next 20 minutes to find a proper example of how to truncate the logs. so, instead of searching again, i am posting it on my site
WARNING: don’t do this unless you have backups or you really, really don’t want to roll your database back. your deleting transaction logs, so while it won’t hurt your working database, it will prevent you from rolling back to yesterday. ye be warned.
in this case, i am running these commands on a microsoft sql server 2005 install, but i would presume it to work on sql 2008 or 2012, although i haven’t tested it.
here is the code:
-- specify database and show database & log statistics
-- truncate the log
BACKUP LOG dbname WITH TRUNCATE_ONLY
DBCC SHRINKFILE (dbname_log, 1)
DBCC SHRINKFILE (dbname_log, 1)
--show statistics after truncating
reference/disclaimer: this code is from http://www.sqlcleanup.com/2008/sql-2005-truncating-log-files-and-recovering-space/ and is not my work, i just can’t always find it in a pinch.
i am working on a script to reboot computers in the middle of the day (sounds crazy, i know). the criteria is that the machines have to not been rebooted in the past 72 hours. why? i have some laptop users that never reboot their machines, just dock and undock their machines (heck, they never even log out), so they are not getting some patches and/or software updates.
while i was working on the script (i plan on posting it when i get done), i ran into some weird results. after digging around a bit, i found out that the problem was that the script was using dns to query the computer with wmi, but was getting multiple results on the dns query. for example, it would look for “computer_01″, and our dns server would give back an ip for the machine. the problem was if you queried “computer_01″ again, you would get a second, different ip address.
so when i went to our dns server, sure enough, there was duplicate entries for the same computer. and a lot of them. obviously this was going to be something i would have to fix before my script would work. after googling a bit, i found out about dns scavenging.
more or less, dns scavenging looks at the age of the dns record and purges it if it hasn’t been used in over x days. i found a great guide and explanation located here:
more or less, i did three things:
- i set scavenging up for 7 days
- i enabled scavenging
- now, the most important step. i waited
three questions came to my mind as i was reading about scavenging:
- why isn’t it enabled by default?
- how did i miss enabling this in the first place? i had some help setting up this domain (it was my first one), but i never saw or heard anything mentioned that i can remember about enabling scavenging.
- are there any downsides to scavenging?
at this point, i have had it enabled a few weeks (again, be patient once you have enabled it) and things have been working great ad the scavenging has definately cleaned things up. for the record, i am running this on a ms windows server 2003 r2 dns server.
now, back to working on that reboot script
one thing we noticed in our install of microsoft office 2007 on our terminal servers was initially the user names and company name were not set correctly.
this didn’t seem like that big of a deal to me at first, but then i saw the value of this user name. if multiple users have access to the same shared folder and two people are trying to access the same file, the second user trying to modify the file with be told that they can only have read-only access because its being used by “user name”. so, in our case, initially everyone’s user name was set to our administrators name.
obviously the user can call us and ask “who is editing the file?” and we can find out, but fixing this keeps us from getting one more call and easier on the user, so i wrote a script that would set it at login.
' This script sets the username and company name correctly for Office products.
' Written because all users using Terminal Services version of office were showing
' the same user name, which made it hard to track down locks on files.
Dim objShell, objNetwork
Dim strUserName, strCompanyName
Dim strNameRegKey, strCompanyRegKey
Set objShell = CreateObject( "WScript.Shell" )
Set objNetwork = CreateObject("Wscript.Network")
' Set your user string and company name
strUserName = objNetwork.UserName
strCompanyName = "Your Company Name"
' Registry key locations for user name and company name
strNameRegKey = "HKCUSoftwareMicrosoftOfficeCommonUserInfoUserName"
strCompanyRegKey = "HKCUSoftwareMicrosoftOfficeCommonUserInfoCompanyName"
On Error Resume Next
' If company name doesn't match the strCompanyName, change it
If objShell.RegRead(strCompanyRegKey) <> strCompanyName Then
objShell.RegWrite strCompanyRegKey, strCompanyName, "REG_SZ"
' If the user's name in office doesn't match their AD user name, change it
If objShell.RegRead(strNameRegKey) <> strUserName Then
objShell.RegWrite strNameRegKey, strUserName, "REG_SZ"
since we did a network overhaul (defined in detail shortly) 2 years ago, we have had zero incidents of malware/virus’/badware on any machines that we know of (i know i will probably walk into bedlam tomorrow morning after saying that ;). after a recent mailing list conversation, i thought i would share some stuff we have done in detail to help anyone else going about the same process. here we go.
the 30 thousand foot view is this:
- we reformatted every workstation and every server in one weekend (yes, you read that correctly)
- users given user only rights, no power users or administrators.
- we leveraged ms gpo’s to hide the c drive, limit what type of files could be saved, and prevent applications from running unless specifically allowed (application whitelist), etc.
- we utilized ms’s wsus to apply patches across the board, workstations and servers
- we started using a gateway device that did web filtering and also implemented a squid proxy server with whitelists
- a copy of advanced installer was purchased so we could create msi’s to push all software out via gpo’s (this is great for new versions of software, pulling back software, etc)
- we used gpo’s to config the workstation so that it automatically logged into our terminal server farm via single sign on (sso), more or less creating a pseudo thin client out of cheap desktop machines
- if you want a user to do/not do something, don’t ask them. force them.
- prevention is the best medicine
this morning on the way to work, i heard larry (from pauldotcom.com) talking about a script he used to pull the logs from a lot of machines. he mentioned a few things that he didn’t like about the script, and i actually had run into in a similar situation (and had similar dislikes). heres the details (and the solution):
for patching purposes and just good windows hygiene, i wanted to reboot all my workstations nightly.
i googled a bit, and found several ways to do this, but none that did it the way i pictured it.
the most frequent suggestion was to put a list of all the computers you wanted to be rebooted into a text file, then run a script against those computers using a wmi script or psshutdown. in my environment, i quickly found two problems with this:
- it was slow. because computers would sometimes be shutdown and the timeout was so long for powered down machines, rebooting hundreds of computers could take a long, long time if enough were shutdown
- laziness/forgetfulness. what are the chances of me and everyone i work with remembering to put new machines into the text file to be rebooted? maybe everyone else is better at this than we are, but i knew this had no shot of actually being kept current in our environment
so i set out to find a script that would reboot all computers in an AD OU, bypassing unavailable machines quickly, and not requiring any changes to the script if new machines were added or machines were removed.
here is what i came up with, see comments in the code for an explanation:
Dim strFilter, strAttrs, strScope, strDNSSuffix, strBase
Dim objConn, objRS, objShell,objExec,objFSO,objFile, fileName
Set objShell = CreateObject("Wscript.Shell")
' List out the OU's you want computers in to be rebooted. Remember, if you add an OU,
' increment the strRoot(x) in *both* places.
strRoot(0) = "OU=warehouse,DC=domain,DC=local"
strRoot(1) = "OU=terminal_servers,OU=servers,DC=domain,DC=local"
' Set the filter for computers only
strFilter = "(objectclass=computer);"
strAttrs = "name;"
strScope = "subtree"
' Your domain name
strDNSSuffix = ".domain.local"
'This is your main loop, each time a different OU.
For i = 0 To UBound(strRoot)
strBase = "<LDAP://" & strRoot(i) & ">;"
Set objConn = CreateObject("ADODB.Connection")
objConn.Provider = "ADsDSOObject"
objConn.Open "Active Directory Provider"
Set objRS = objConn.Execute(strBase & strFilter & strAttrs & strScope)
'This is your inner loop, each time an individual PC found in the search of the base.
While Not objRS.EOF
If objRS.Bookmark Mod 1 = 0 Then
' Pause for two seconds (ran into issues if this moved too quick)
' Call your shutdown computer sub to reboot the individual computers
ShutDownComputer(objRS.Fields("name").Value & strDNSSuffix)
Set objConn = Nothing
Set objRS = Nothing
Sub ShutDownComputer(byval strComputer)
' Your psshutdown command with the following options: -r for reboot, -f for force, -c allow the user to cancel the shutdown, -t 300 to give the user
' 300 seconds (5 minutes) to close out of applications and save, -e for the error code (planned), and -m for the message the user will see
strShutDown = "C:somepathpsshutdown.exe -r -f -c -t 300 -e p:0:0 -m " & chr(34) & "Nightly restart of computer" & chr(34) & " \" & strComputer
Set objShell = CreateObject("WScript.Shell")
objShell.Run strShutdown, 0, False
Set objShell = Nothing
psshutdown – http://technet.microsoft.com/en-us/sysinternals/bb897541.aspx
pauldotcom episode 187 notes – http://pauldotcom.com/wiki/index.php/Episode187