Just a quick 101 style video I put together to show the pieces of a SQL Server database for those that are starting to learn the product. Also a teaser for my teaching style of my DBA Fundamentals classes!
Enjoy!
SQL Server Database Consulting
Just a quick 101 style video I put together to show the pieces of a SQL Server database for those that are starting to learn the product. Also a teaser for my teaching style of my DBA Fundamentals classes!
Enjoy!
Now that the official schedule has been published, I am pleased to announce that I will be doing a pre-con and a regular session at SQL Saturday Minnesota on October 6-7. 2017.
The Friday pre-conference will be “DBA Fundamentals for the Junior, Accidental and non-DBAs.” This will be a wide ranging day-long discussion on the underlying fundamental pieces of SQL Server Administration that are beneficial to both new DBAs, and those that have to work with or fill in for them. Please see the link for details, and feel free to contact me with any questions. There is a $110 (plus service fees) charge for this, which is split between me and the local SQL User group organizing the event, after expenses.
The Saturday regular session will be “Backups for new/non-DBAs…the Why, not the How”, where we will cover Disaster recovery scenarios and the part SQL Server plays in the recovery. We won’t spend much time on specifically defining backup types, etc. Currently this is scheduled for the 1-2pm time slot, but that may change.
Edit: my Saturday session is now 10:15am – 11:15am 🙂
I’m very excited to be coming to the MSP area! I even have a friend that lives in the area that I get to see for the first time in 3 years…plus all of the new friends I will make at the events!
Thanks,
Kevin3NF
If you haven’t been asked to Delete a database in your DBA career…its just a matter of time:
“We don’t need database XYZ anymore…delete it.” — Some well meaning manager
Stop right now, before blindly following an order like that.
First…there are some questions to ask:
As a DBA, any time anyone asks you to delete anything, from data to an entire database, you need to question it. Not doing so can be a CLM/RGE combo! (Career Limiting Move/Resumé Generating Event)
The request may be perfectly valid, but in my experience most people that ask for a database to be deleted use the term to mean “I don’t want to see that database in SSMS anymore” or “I don’t want it on Server A, put it on Server B”, with very little regard for impact to other teams or applications.
There are three main ways to take a database “out of service”:
Each has its own considerations and each is the proper choice in different circumstances. I am going to walk you through each one, from least risky to most, in that order.
Takes the database offline, but still visible in SSMS. Cannot be accessed by applications, backups, etc. Can be brought back online relatively quickly.
My list of databases before starting this post…we’ll be messing with the Rename2222 database (nothing in it…came from a rename script I was writing/testing):
Right-click the database in question, Tasks, Offline:
Dialog Box you get after clicking “Take Offline”:
Results in SSMS after Offline Operation:
Upside to Offline method: Database is still in place, visible in SSMS, Record in master database is still there, relatively simple.
Downside to Offline: May cause SQL Monitoring tools to throw alerts and create tickets, files are not in use by SQL Server, can be deleted.
Removes the database from SSMS, deletes its meta information in the master database, but leaves the physical files intact on the drive.
The detach menu:
The detach dialog:
After Detaching:
Note that the MDF/LDF files are still right where they were. SQL Server no longer has a handle on them:
You can re-attach fairly quickly should the need arise (i.e. user comes screaming at you 5 minutes after the detach):
Upside to Detach method: Files are still in place, re-attach is fairly straightforward, Monitoring tools will generally not error. Can save your job by not having deleted a database someone else needed.
Downside to Detach: Files not manually deleted take up disk space…set reminders to delete after some time period.
Drops the database, its meta data in Master, AND its associated physical files…you cannot reverse a Delete (DROP Database).
This is one of those “Stop right here” moments. Before you Delete a database, make very sure you are following protocols. Make sure you have a recent FULL backup:
If not, run one. The simplest possible command to backup a database:
-- backs up to default location Backup Database Rename2222 To Disk = 'Rename2222.bak'
Better:
-- backs up to specified location -- with date included in the file name: Backup Database Rename2222 To Disk = 'c:\MyBackups\Rename2222_20170531.bak'
Once you have a backup, you also should get a ticket, change control, or at the very minimum an email from the requester with approval for the delete. Depends on your process. Especially important if this is a production environment. This can save your job. If you Delete a database that a system depends on and cause an outage…whoever told you to do may claim they were not involved.
To delete is way too simple:
Remember…once you Delete a database…its gone. The ONLY ‘undo’ is to restore from Backup. You can call Microsoft, but they will tell you the same thing. Worst case, you can spend tens of thousands of dollars on a data recovery service, assuming you can take the server down and nothing overwrote the space the the files were in on the drives.
Almost everywhere I’ve worked, Detach with a 2-4 week follow up to delete the files was the best choice. But beware that some systems may only be useful for Quarterly or Annual reporting. In an ideal situation, you have a complete inventory of every database on every server from Production down to Dev and know exactly why they are there, as well as who the stakeholders are for each one. If you don’t have this for the O/S, SQL and DB level info, start now.
Thanks for reading!
Day 1 of the free DBA fundamentals was a huge success!! Thanks to all that came out.
Day 2 (June 7) registration goes live at noon on Saturday, May 27 (still free):
This is an in-person class…not online and not recorded. We are not that fancy yet…
We will be covering the following:
You do not need to bring a laptop, but if you have one with SQL Server and SSMS installed to follow along, please feel free to bring it.
You do need to be registered to attend…we completely filled our space last Wednesday, with no room for walk-ins. Please do not just show up and make me have you stand in the corner 🙁
There will be a waitlist when the tickets are gone.
If you came to Day 1 and sent me a request, you are already registered for Day 2.
Thanks, and see you there on June 7!
Kevin3NF (follow me on Twitter…its your homework!)
Free. In person. Interactive.
Update: This is in-person only…not online or Webex at this time.
I really like training and teaching the basics of anything to people new to it. Right now that is SQL Server, and cycling (I am Kevin3NF or CoachKev depending…). Every day on the various forums I see the same questions over and over on basic tasks…and the askers go away with a solution but very often they don’t get the understanding to be able to resolve the issue on their own the next time.
So…if you are in or near the Dallas/Ft. Worth, Texas area and have a couple of hours free after work once every 2 weeks come join me for some very informal training.
Target audience:
If you are already a DBA, this is probably not for you unless you are just starting out.
5 weeks, every other Wednesday 6-8pm starting on May 24, 2017 in Richardson, TX.
If this goes as well as I hope, I will offer it again after some time off. Maybe in a different part of the Metroplex.
Not sure if this class is right for you? Ask in the comments, send me a DM on Twitter, or email me.
Thanks for reading!
Follow Dallas DBAs on LinkedIn:
In my Top 10 SQL Server Functions post awhile back, I listed DBCC OPENTRAN as one of the top 3, and for good reason.
An Open transaction may simply be something that has not finished yet, or someone issued a BEGIN TRAN without a corresponding COMMIT or ROLLBACK. Or as we will see at the end, replication is having issues.
You can use this against any database with minimal syntax and get back solid information very quickly.
--connect to sample db use MyDatabase go --as generic as this command gets and still runs: DBCC OPENTRAN
Result if nothing is open:
If I start and execute a DML (insert, update or delete) transaction with BEGIN TRAN and leave out the corresponding COMMIT, I get:
Now, if I open a second transaction (in a new query window) and execute any DML statement without the COMMIT, and then run DBCC OPENTRAN again, I get:
Yes…the same output, as this is just showing the ONE oldest transaction.
I can run a query to show that there are two SPIDs with open transactions:
-- SELECT spid, blocked,[dbid],last_batch,open_tran FROM master.sys.sysprocesses WHERE open_tran <> 0
If I COMMIT spid 64 and re-run DBCC OPENTRAN, the SPID changes to the second transaction I started:
If I COMMIT spid 52 and re-run DBCC OPENTRAN along with checking sysprocesses for open_tran <> 0 I get:
Now, all of that was just running DBCC OPENTRAN by itself. There are additional options:
--specify dbname, dbid or 0 for the current database DBCC OPENTRAN (SmallData_BigLog)
You will get results in the same format as the previous examples.
You can suppress all messages, regardless of if a transaction is open or not (but I have no idea why this would help you…)
DBCC OPENTRAN (0) with no_infomsgs
Result:
If you needed to periodically capture the oldest transaction, in order to review later, use WITH TABLERESULTS:
-- TableResults only shows the oldest open tran -- useful running in a loop to load the oldest -- tran over time. --create a temp table CREATE TABLE #OpenTranStatus ( ActiveTransaction varchar(25), Details sql_variant ); -- Execute the command, putting the results in the table. INSERT INTO #OpenTranStatus EXEC ('DBCC OPENTRAN (SmallData_BigLog) with tableresults') SELECT * FROM #OpenTranStatus DROP TABLE #OpenTranStatus
In the above, you could create a user table instead of a temp table of course…it depends on your needs.
One more particularly useful item you may see when running DBCC OPENTRAN by itself:
If your database is participating in Replication as a Publisher, this may show up when running OPENTRAN, but it doesn’t necessarily mean that the transaction is actually open. I set this up and stopped the Replication Log Reader and Distribution agent jobs. I then added some data to a published table (article) and ran DBCC OPENTRAN to get the above result. Note that there are two lines with LSN information in them (no SPIDs)
I then ran the Log Reader Agent job and got back:
I have verified that new records I inserted have been read by the log reader, AND distributed to the subscriber(s). This means that while you are seeing
There is not an error…just info.
If you have non-distributed LSNs, there is something to troubleshoot in the replication process which is way outside the scope of this post. A non-distributed replicated transaction/LSN CAN cause some huge Log file growth, and need to be investigated. If this happens frequently, use the TABLERESULTS option to log to a regular table and alert on it.
Hopefully this gives you some insight into various ways to use DBCC OPENTRAN as well as use cases for the various options. 90% of the time I run this, it is due to application transactions timing out, or log file growth issues.
I love comments….please feel free to leave questions for me in them on this topic.
Thanks for reading!