July 17, 2009 at 8:26 am
According to Steve I Ran Profiler, setting filter to model db and wanted to see what's occurring in there. Still I didn't get any clue. Several activities I noticed but all are different select command like :
use [model]
SELECT
is_member(N'db_accessadmin') AS [IsDbAccessAdmin],
is_member(N'db_backupoperator') AS [IsDbBackupOperator],
is_member(N'db_datareader') AS [IsDbDatareader],
is_member(N'db_datawriter') AS [IsDbDatawriter],
is_member(N'db_owner') AS [IsDbOwner],
is_member(N'db_securityadmin') AS [IsDbSecurityAdmin],
is_member(N'db_ddladmin') AS [IsDbDdlAdmin],
is_member(N'db_denydatareader') AS [IsDbDenyDatareader],
is_member(N'db_denydatawriter') AS [IsDbDenyDatawriter],
is_member(N'db_owner') AS [DboLogin],
user_name() AS [UserName],
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
SUM(a.total_pages) AS [SpaceUsed],
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in (1, 3)) AS [LogSize],
SUM(CASE WHEN a.type 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) AS [DataSpaceUsage],
SUM(a.used_pages) AS [IndexSpaceTotal],
(select count(1) from sys.services where name ='InternalMailService') AS [IsMailHost],
(select default_schema_name from sys.database_principals where name = user_name()) AS [DefaultSchema],
(select df.physical_name from sys.database_files as df where 1=df.data_space_id and 1 = df.file_id) AS [PrimaryFilePath]
FROM
sys.allocation_units AS a INNER JOIN sys.partitions AS p ON (a.type = 2 AND p.partition_id = a.container_id) OR (a.type IN (1,3) AND p.hobt_id =
a.container_id)
Run Profiler, set to filter to the model database and look for activity. Something is occurring in there.
[/qiote]
There is another commad
dbcc sqlperf(logspace)
another one-
create table #tmplogspc (DatabaseName sysname, LogSize float, SpaceUsedPerc float, Status bit)
nsert #tmplogspc EXEC ('dbcc sqlperf(logspace)')
SELECT
s.name AS [Name],
s.physical_name AS [FileName],
CAST(CASE s.is_percent_growth WHEN 1 THEN s.growth ELSE s.growth*8 END AS float) AS [Growth],
CAST(CASE when s.growth=0 THEN 99 ELSE s.is_percent_growth END AS int) AS [GrowthType],
s.file_id AS [ID],
CASE when s.max_size=-1 then -1 else s.max_size * CONVERT(float,8) END AS [MaxSize],
s.size * CONVERT(float,8) AS [Size],
tspclog.LogSize * tspclog.SpaceUsedPerc * 10.24 AS [UsedSpace],
CAST(case s.state when 6 then 1 else 0 end AS bit) AS [IsOffline],
s.is_read_only AS [IsReadOnly],
s.is_media_read_only AS [IsReadOnlyMedia],
s.is_sparse AS [IsSparse]
FROM
sys.master_files AS s
INNER JOIN #tmplogspc tspclog ON
tspclog.DatabaseName = db_name()
WHERE
(s.type = 1 and s.database_id = db_id())
ORDER BY
[Name] ASC
drop table #tmplogspc
actually I'm not feeling good - why my model log is grouing up surprisingly.
I stopped backup process three days before,if any backdoor backup is happening I don't know. But from my side no backup is running(I'm worried without backup).
Though it's a text box (server) but my data are valid data and I have to move to the production server by the next week.I can't loss them. I imported them from last november which are very very important for my company.
Again I'm not doing anything in model db.
What should I do now? I also have a concern, if I truncate any log file will it be a problem when I take backup and move to the new server.
Please need your valuable sugessions.
Thanking you all again.
Maksuda...
July 17, 2009 at 8:55 am
Being a dev environment I would follow Jason's suggestion... set model database to read-only, sit down and wait from somebody to come to your office and complain.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 17, 2009 at 9:00 am
h_maksuda (7/17/2009)I stopped backup process three days before,if any backdoor backup is happening I don't know. But from my side no backup is running(I'm worried without backup).
mmhhh... may be Lynn is right. Is it your TLog or your TLog dump file that is growing?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 17, 2009 at 9:01 am
Thanks Jason and PaulB.
I set model db to read only.
Next for the large log file (model log)I should do somthing.Now it's more than 22 GB great alarming for me.
Thanks in advance
Maksuda...
July 17, 2009 at 9:45 am
mmhhh... may be Lynn is right. Is it your TLog or your TLog dump file that is growing?
[/quote]
Thanks PaulB
It's dump file or not I have no clue........I only found it's model log file.
Is there any way to find it out if it's a dump file, please.
Thanking You,
Maksuda...
July 17, 2009 at 9:55 am
Maybe run this:
select * from msdb.sys.database_files and post the results?
Also might be worth considering turning the default auto-grow settings off or resticting the growth quite a bit. In most cases I can't imagine why you would need more than 50MB or so for data or log files in the model database. (And even that may be overkill).
The Redneck DBA
July 17, 2009 at 10:03 am
That profiler statement shows a creation of a temp table and then an insert of logspace information. Nothing should be happening in the model database. If you set the model database to read only, then the log shouldn't grow.
I'm confused, how are you checking the size of the log? Are you looking in the file system through the Windows Explorer?
Also, please be careful and give us details when you type. It's hard to tell what is wrong. For example, you have model.lgd in your title. Model would usually have modellog.ldf.
You should not have any data in model, but it should be in other databases. you ought to be able to set up a maintenance plan for all user databases, which would not include model.
Please also check the properties of the model database, and be sure that the file that you say is growing is listed as a file for model.
July 17, 2009 at 11:59 am
Thanks Jason.
Very begining I didn't look at the model db log file (It's my fault). When I ran out of space I looked at all files and then found model log file size is 22GB. And allmost all the space are occupied by log.
Here is the query result: select * from msdb.sys.database_files
file_id file_guide type type_desc data_space_id name
physical_name state
state_desc size max_size growth is_media_read_only is_read_only is_space
is_percent_growth is_name_reserved create_lsn drop_lsn read_only_lsn read_write_lsn differential_base_guide differential_base_time redo_start_lsn redo_start_gork_guide redo_target_lsn redo_target_gork_guide backup_lsn
1687FF05A-A3DB-416D-84C0-CD63BB084B5C0ROWS1MSDBDataC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf0ONLINE856-13200000NULL
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
21FDF1764-5690-46B8-9B16-A07E538B5CAD1LOG0MSDBLogC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf0ONLINE962684354563200000NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
hope you could understand the result
That profiler statement shows a creation of a temp table and then an insert of logspace information. Nothing should be happening in the model database. If you set the model database to read only, then the log shouldn't grow.
Steve actually I set read only today morinng, I created profiler last evening and keep it open to get all the statements for model db.
I'm confused, how are you checking the size of the log? Are you looking in the file system through the Windows Explorer?
I checked size in different way. first from windows explorer, I executed the query :
dbcc sqlperf(logspace)
which gives me the result:
Database Name LogSize(inMB) Log Space Used(%) Status
master 0.742187563.15789 0
tempdb 1.99218859.41177 0
model 21873.390.91385 0
msdb 0.742187567.89474 0
SecurityReportingSystem 459.11723.428051 0
Northwind 0.992187554.57677 0
IssNetwork 37.67969 16.27229 0
Also, please be careful and give us details when you type. It's hard to tell what is wrong. For example, you have model.lgd in your title. Model would usually have modellog.ldf.
My apology, that was a typing error, it's modellog.ldf file.
I didn't set up any maintenance plan yet, feel to do it very recent.
Also growing file is modellog.ldf file.
Thank you guys.
Maksuda...
July 17, 2009 at 12:20 pm
If I'm counting over to the right column, then for the data file you have 856 Pages (under 8MB) and only have 96 Pages (< 1MB) in the log file.
The Redneck DBA
July 17, 2009 at 12:27 pm
There is definitely something filing into the model database. Run the following to see if there are any user objects in this database:
Use model;
Select *
From sys.objects o
Where o.is_ms_shipped 1;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 17, 2009 at 12:39 pm
We should also take a look in the transaction log to see what transactions are being execute - but, that is going to be a lot of data to search through.
SELECT ... FROM fn_dblog(Null, Null);
The above will display all of the transactions, again - that will output a lot of data. Try only output the top 10000 to see a sample of the transactions, as in:
SELECT TOP 10000 * FROM fn_dblog(Null, Null);
And finally, run the following and post the results:
Select name
,d.recovery_model_desc
,d.log_reuse_wait_desc
From sys.databases d
Where name = 'model';
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 17, 2009 at 12:48 pm
Jason Shadonix (7/17/2009)
If I'm counting over to the right column, then for the data file you have 856 Pages (under 8MB) and only have 96 Pages (< 1MB) in the log file.
That is correct for the MSDB database. However, the problem for the OP is the MODEL database - and specifically the model log file.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 17, 2009 at 1:11 pm
Thanks guys. Jeffrey, here is the result of the query-
Select name
,d.recovery_model_desc
,d.log_reuse_wait_desc
From sys.databases d
Where name = 'model';
DBName recovery_model_desc log_reuse_wait_backup
model FULL LOG_BACKUP
I ran the query : SELECT TOP 10000 * FROM fn_dblog(Null, Null);
but I didn't get any clue.......
I'm afraid I have to do somthing by next two hours. today is friday and next two days are week-end.
By this time my server might crash.
So should I follow the procedure that Jeffrey mentioned earlier of this thread?
pls need help...........
thanking you
Maksuda...
July 17, 2009 at 2:09 pm
You still need to identify what is writing to the model database. The transaction log will not grow unless there is activity being performed in the database. Not knowing what that activity is - modifying the database and shrinking the log file is only a temporary solution.
If you don't identify the problem - it will come back and potentially cause you even more issues.
In the meantime, your last message shows that the model database is still set to FULL recovery model and the transaction log cannot be reused until a LOG BACKUP is performed.
So, I would do the following:
1) ALTER DATABASE model SET RECOVERY SIMPLE;
2) CHECKPOINT;
3) DBCC SHRINKFILE('modellog', 10);
I would leave it this way until you can find out what is writing to this database.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 17, 2009 at 2:24 pm
Maksuda... something does not adds up.
I understand model database was set to read-only mode, is that correct?
I understand tlog for model database keeps growing, is that correct?
If answers are Yes & Yes we have to go back to the drawing table. A database that is not allowing updates/inserts/deletes cannot generate tlog.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply