April 28, 2015 at 10:47 pm
HI all,
I just notice that my MASTER database has some user tables and user SP ( I have no idea who created it ) ..and I am thinking to move them to 1 new user database but I am worried it will break something ..
What should I do ?
Moreover I wonder why Transaction log of MASTER can be full ( The recovery model is simple ) It should be fine , isn’t it ?
Really appreciate for the feedback
Cheers
April 29, 2015 at 3:02 am
Recovery for master should be simple.
Don't move user objects if you're not sure nobody's using them.
-- Gianluca Sartori
April 29, 2015 at 5:13 pm
Since the default db is master, often things get built there by accident. You can create a DDL trigger to prevent that from happening again, if you want.
You can also move them out of master by creating a synonym in master that points to the new location.
To find any existing references, search through the sys.sql_.sql_expression_dependencies in each db to find code directly referencing those objects. You should also look in the job step commands for references. I'm sure there are some other places as well.
When you're ready, just drop the synonym temporarily and see if anyone howls / something breaks :ermm:.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 29, 2015 at 7:00 pm
Much appreciate on your reply !
Honestly I am still not sure about “creating a synonym in master that points to the new location.” Would you please explain in more detail ?
Step by step would be great if you don’t mind .
Btw I have an issue with transaction log of MASTER .. It is full (Log file size: 1792 Remaining free space avaliable to log: 0 )
What should I do ? I am not allowed to backup transaction Log for MASTER
Cheers mate
April 30, 2015 at 1:38 am
This means that master is not being backed up. Set the recovery model to simple and shrink the log file to a reasonable size (reasonable = data file size, which should be small).
As far as synonyms is concerned, you just have to move the objects to another database (e.g. MyDatabase) and then run:
USE master;
GO
CREATE SYNONYM MyTable FOR MyDatabase.dbo.MyTable;
GO
-- Gianluca Sartori
April 30, 2015 at 8:14 am
I wonder why Transaction log of MASTER can be full ( The recovery model is simple )
You can't back up a log in simple recovery model (because the log would not be useful in recovery anyway).
If the log is full and not truncating, there must be some transaction holding it up, possibly due to the user db in master.
Run these command and see what results you get:
USE master;
DBCC OPENTRAN;
If you get:
No active open transactions.
And the master db is in simple mode, the log should be truncating normally. If it's still full, allocate more space to it until you can figure out what's going on with it.
You can see what % of the log files are in use by running this command:
DBCC SQLPERF(LOGSPACE);
One final very minor point:
In the CREATE SYNONYM command, be sure to specify the schema for the synonym name itself, just be absolute sure you don't pick up the wrong one:
CREATE SYNONYM dbo.MyTable ...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply