Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [system-databases]

41 questions
8
votes
3 answers

Is it possible for master, tempdb, model and msdb to have a database_id other than 1,2,3,4 respectively?

SQL Server System Databases, as far as I know, always have those same IDs and I have seen lots of maintenance scripts on the internet relying on the predicate WHERE database_id > 4 to exclude them from the actions of the script. Also, if I run…
Ronaldo
  • 3,985
  • 1
  • 7
  • 38
5
votes
1 answer

Could not obtain exclusive lock on database 'model' - who is using it?

when trying to run the following query in order to create a new database: CREATE DATABASE [Lunch] CONTAINMENT = NONE ON PRIMARY ( NAME = N'Lunch', FILENAME = N'E:\Data Files\Lunch.mdf' , SIZE = 110592KB , FILEGROWTH = 1048576KB…
Marcello Miorelli
  • 14,544
  • 46
  • 126
  • 249
4
votes
2 answers

Change drive letter (which contains system dbs)

Is it possible to safely change the drive letter for a volume which holds only system databases? What precautions should be taken and how should it be done? I know I can just go to computer management > storage and change drive letter but can have…
WhoIsNinja
  • 157
  • 1
  • 2
  • 6
4
votes
3 answers

Does SQL Server re-create system databases if they are lost/missing?

We have a third-party server backup tool running every 24 hours, which backs up everything (files and database in preparation for a bare-metal restore). After much investigation, it emerged that the tool used non copy_only backups, thus destroying…
EvilDr
  • 778
  • 1
  • 10
  • 23
3
votes
1 answer

want to find out which databases are used in last 30 days or not

I have about 120 databases in my SQL. I want to migrate so I want to know which databases are not used in last 30 days.
Mike
  • 31
  • 2
3
votes
2 answers

Where can I get the master.dacpac file for SQL Server 2012?

I am using Visual Studio 2012 Professional with SQL Server Data Tools with a SQL Server 2012 standard edition database instance. I have been unable to locate a master.dacpac file on either my development machine or on the server hosting the SQL…
Mark Freeman
  • 1,938
  • 5
  • 24
  • 43
3
votes
2 answers

Where are SQL Server catalog views persisted?

It seems that sys catalog views can be found under every database, either built in or user created. Are they all actual view definitions created under each of the database or just "links" to somewhere? Thanks
user99201
3
votes
1 answer

Migrating system and user databases to a new server by moving the LUN

I have to replace a server. The system and user databases all reside on the E: drive which is a SAN LUN. The method they want to employ is to remove the LUN from the current server and present it to the new server. My concerns are that SQL Server is…
Windsor19
  • 31
  • 1
2
votes
2 answers

CHECKDB fails on MSDB and Master system databases

I get the following error within my maintenance plans: Executing the query "DBCC CHECKDB(N'master') WITH NO_INFOMSGS " failed with the following error: "The database could not be exclusively locked to perform the operation. Check statement…
Sean Perkins
  • 1,355
  • 4
  • 25
  • 37
2
votes
3 answers

Will SQL Server go offline if it loses network connectivity to SAN where master and msdb system databases reside?

I have a setup where 3 servers combined into Availability Group All 3 servers have local directly attached SSD drives, and user database files are hosted on these drives But the system databases (master and msdb) of each server in AG, are hosted on…
Aleksey Vitsko
  • 4,308
  • 3
  • 21
  • 47
2
votes
1 answer

SQL statement to hide all system databases in Server from a server login

I want to create a Windows authentication server login and make it cannot see any system databases e.g.master, model. I know that we can control view definition in database level e.g. database user, database role but I don't want to configure them…
theeranitp
  • 121
  • 4
2
votes
1 answer

When is autoclose OK to be not OK

By chance, I happened to see that the autoclose feature on SQL dbs (I'm using SQL 2008) shouldn't be on and I found that all of my SQL dbs were set to have autoclose on. I've set autoclose to off on them all (I have about 400 dbs of varying sizes on…
Tom Brown
  • 37
  • 3
2
votes
1 answer

Permissions to be granted to developers to investigate jobs, queries, and packages

I have just installed a new sql server 2014 server, migrated some databases there and connected some applications to them. These servers have also SSAS and SSIS. I have a group of developers there were helping with the connections and doing all the…
Marcello Miorelli
  • 14,544
  • 46
  • 126
  • 249
1
vote
2 answers

How Can I Read Where TempDB's Files Will Be Created After Restoring Master DB?

I'm restoring to an alternate server in a DR drill and have restored master but can't restart the instance, even with /f and /m switches. The log is indicating tempdb can't be created, probably looking for the paths from the source instance. How can…
MattyZDBA
  • 1,913
  • 2
  • 19
  • 26
1
vote
2 answers

Does leaving a connection open for a matter of seconds cause deadlocks?

I have been testing a system application and have noticed that it is slow when it connects to the database overseas. This is to be expected to some extent, but there is a big difference with the domestic version, and after investigation realized…
Damien Golding
  • 197
  • 4
1
2 3