Check if im backing up my databases

Is there a way I can find out if my databases are being backed up daily on a
daily basis on a server ?
I want to be able to run a script and list all databases not being backed up
dailyHassan
If you build database maintenance plan and check all user database , you
are sure that all databases are being backed up
as well as theit log files (I assume with FULL recovery mode)
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
> Is there a way I can find out if my databases are being backed up daily on
> a daily basis on a server ?
> I want to be able to run a script and list all databases not being backed
> up daily
>|||Something like
select d.name as [Database],
max(b.backup_finish_date) as [Last Backup],
case
when datediff(hh,isnull(max(b.backup_finish_date),'19000101'),getdate()) >
24
then 'N' else 'Y' end as [Recent Full Backup]
from master.dbo.sysdatabases d
left join msdb.dbo.backupset b
on d.name = b.database_name and b.type = 'D'
group by d.name
order by d.name
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
> Is there a way I can find out if my databases are being backed up daily on
> a daily basis on a server ?
> I want to be able to run a script and list all databases not being backed
> up daily
>|||Hi
ALTER DATABASE DataBase SET RECOVERY FULL
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ulbMSq8AGHA.3104@.TK2MSFTNGP12.phx.gbl...
> Something like
> select d.name as [Database],
> max(b.backup_finish_date) as [Last Backup],
> case
> when datediff(hh,isnull(max(b.backup_finish_date),'19000101'),getdate()) >
> 24
> then 'N' else 'Y' end as [Recent Full Backup]
> from master.dbo.sysdatabases d
> left join msdb.dbo.backupset b
> on d.name = b.database_name and b.type = 'D'
> group by d.name
> order by d.name
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
>> Is there a way I can find out if my databases are being backed up daily
>> on a daily basis on a server ?
>> I want to be able to run a script and list all databases not being backed
>> up daily
>|||Sorry, wrong post
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e1uO608AGHA.4036@.TK2MSFTNGP10.phx.gbl...
> Hi
> ALTER DATABASE DataBase SET RECOVERY FULL
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:ulbMSq8AGHA.3104@.TK2MSFTNGP12.phx.gbl...
>> Something like
>> select d.name as [Database],
>> max(b.backup_finish_date) as [Last Backup],
>> case
>> when datediff(hh,isnull(max(b.backup_finish_date),'19000101'),getdate())
>> > 24
>> then 'N' else 'Y' end as [Recent Full Backup]
>> from master.dbo.sysdatabases d
>> left join msdb.dbo.backupset b
>> on d.name = b.database_name and b.type = 'D'
>> group by d.name
>> order by d.name
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Hassan" <Hassan@.hotmail.com> wrote in message
>> news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
>> Is there a way I can find out if my databases are being backed up daily
>> on a daily basis on a server ?
>> I want to be able to run a script and list all databases not being
>> backed up daily
>>
>

Check if im backing up my databases

Is there a way I can find out if my databases are being backed up daily on a
daily basis on a server ?
I want to be able to run a script and list all databases not being backed up
daily
Hassan
If you build database maintenance plan and check all user database , you
are sure that all databases are being backed up
as well as theit log files (I assume with FULL recovery mode)
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
> Is there a way I can find out if my databases are being backed up daily on
> a daily basis on a server ?
> I want to be able to run a script and list all databases not being backed
> up daily
>
|||Something like
select d.name as [Database],
max(b.backup_finish_date) as [Last Backup],
case
when datediff(hh,isnull(max(b.backup_finish_date),'1900 0101'),getdate()) >
24
then 'N' else 'Y' end as [Recent Full Backup]
from master.dbo.sysdatabases d
left join msdb.dbo.backupset b
on d.name = b.database_name and b.type = 'D'
group by d.name
order by d.name
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
> Is there a way I can find out if my databases are being backed up daily on
> a daily basis on a server ?
> I want to be able to run a script and list all databases not being backed
> up daily
>
|||Hi
ALTER DATABASE DataBase SET RECOVERY FULL
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ulbMSq8AGHA.3104@.TK2MSFTNGP12.phx.gbl...
> Something like
> select d.name as [Database],
> max(b.backup_finish_date) as [Last Backup],
> case
> when datediff(hh,isnull(max(b.backup_finish_date),'1900 0101'),getdate()) >
> 24
> then 'N' else 'Y' end as [Recent Full Backup]
> from master.dbo.sysdatabases d
> left join msdb.dbo.backupset b
> on d.name = b.database_name and b.type = 'D'
> group by d.name
> order by d.name
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
>
|||Sorry, wrong post
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e1uO608AGHA.4036@.TK2MSFTNGP10.phx.gbl...
> Hi
> ALTER DATABASE DataBase SET RECOVERY FULL
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:ulbMSq8AGHA.3104@.TK2MSFTNGP12.phx.gbl...
>

Check if im backing up my databases

Is there a way I can find out if my databases are being backed up daily on a
daily basis on a server ?
I want to be able to run a script and list all databases not being backed up
dailyHassan
If you build database maintenance plan and check all user database , you
are sure that all databases are being backed up
as well as theit log files (I assume with FULL recovery mode)
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
> Is there a way I can find out if my databases are being backed up daily on
> a daily basis on a server ?
> I want to be able to run a script and list all databases not being backed
> up daily
>|||Something like
select d.name as [Database],
max(b.backup_finish_date) as [Last Backup],
case
when datediff(hh,isnull(max(b. backup_finish_date),'19000101'),getdate(
)) >
24
then 'N' else 'Y' end as [Recent Full Backup]
from master.dbo.sysdatabases d
left join msdb.dbo.backupset b
on d.name = b.database_name and b.type = 'D'
group by d.name
order by d.name
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
> Is there a way I can find out if my databases are being backed up daily on
> a daily basis on a server ?
> I want to be able to run a script and list all databases not being backed
> up daily
>|||Hi
ALTER DATABASE DataBase SET RECOVERY FULL
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ulbMSq8AGHA.3104@.TK2MSFTNGP12.phx.gbl...
> Something like
> select d.name as [Database],
> max(b.backup_finish_date) as [Last Backup],
> case
> when datediff(hh,isnull(max(b. backup_finish_date),'19000101'),getdate(
)) >
> 24
> then 'N' else 'Y' end as [Recent Full Backup]
> from master.dbo.sysdatabases d
> left join msdb.dbo.backupset b
> on d.name = b.database_name and b.type = 'D'
> group by d.name
> order by d.name
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
>|||Sorry, wrong post
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e1uO608AGHA.4036@.TK2MSFTNGP10.phx.gbl...
> Hi
> ALTER DATABASE DataBase SET RECOVERY FULL
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:ulbMSq8AGHA.3104@.TK2MSFTNGP12.phx.gbl...
>

check if cast is possible

I use to import data from DBF Clipper databases into SQL Server. When a table is just imported its date fields have string format. I need to copy their data to tables of database where they ahve to be converted into date. Direct operator INSERT doesn't convert properly (I've not successed in changing default date format so it'll be covertable) but using CAST I can get result of strings like 13.05.1970 0:00:00 as datetime type. But not all records can be coverted this way. For ones can't be converted I've solved to make NULL fields there. But I don't know how to make CAST operation return NULL when convertion isn't possible. The query
INSERT INTO people_temp
(reg_num, surname, stname, patronymic, foreing, gender, birthdate, fam_pos, dwell_type, children, nation, par_not, region, stud_fml, parn_fml,
com_prob, sp_prob, sn_passport, nn_passport, dv_passport, wg_passport)
SELECT STUDENTs_temp.REG_NOM, STUDENTs_temp.FAMILY, STUDENTs_temp.NAME, STUDENTs_temp.PARN_NAME, STUDENTs_temp.INOSTR,

STUDENTs_temp.SEX,
CAST(PSPR_temp.DATA_BORN AS smalldatetime), PSPR_temp.SEM_POL, PSPR_temp.XAR_JT, PSPR_temp.CHILDREN,

PSPR_temp.NATION,
PSPR_temp.SV_ROD1 + PSPR_temp.SV_ROD2 AS Expr1, PSPR_temp.REGION, PSPR_temp.STUD_FML,

PSPR_temp.PARN_FML,
PSPR_temp.OB_STAJ, PSPR_temp.SP_STAJ, PSPR_temp.SN_PASPORT, PSPR_temp.NN_PASPORT, PSPR_temp.DV_PASPORT,
PSPR_temp.WG_PASPORT
FROM STUDENTs_temp INNER JOIN
PSPR_temp ON STUDENTs_temp.REG_NOM = PSPR_temp.REG_NOM
gets an error 'The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value'. Tell me please how can make type casting return NULL if convertion isn't possible.I might try something like so:

Insert Into MyTable(MyDateField)
SELECT (CASE WHEN isDate(DateFieldTobeImported) = 1 Then DateFieldTobeImported ELSE Null End)
From TableBeingImported

If this does not work, please post some sample data.|||Thank you, it works.

Check for missing identity numbers

Is there a way to check for missing identity numbers in a Primary Key column? I have some databases that are not fully normalized and want to check on tables that might have had some records deleted. Thank you.

Do you really want to do this in SSIS? Whilst possible I think a SQL based solution would be much faster, and probably makes more sense. A quick Google will come up with plenty of script samples e.g. http://www.nigelrivett.net/FindGapsInSequence.html|||

If you just need to know the number of 'deleted' rows; you can do a simple substraction of the max identitity number minus the number of rows in the table. If you need a list of the ID's that not exists in the table; then you could use a cursor that from 1 to max identity and checks if the row exists in the table or not....anyway you may find more help in the t-sql forum that is next door

Check for inactive database

Hi

I am working at a site that has no dedicated DBA, and our Sql Server 2000 environment has recently been playing up. There are several databases on the Production server that should be inactive (for applications no longer used).

What is the best way for me to see if there has been any recent activity on these databases (short of taking them offline and waiting for screams). Please keep in mind that I am a Sql Server novice.

Cheers

AFAIK ....there is no simple method or query to find which database is used when. The best method is to run profiler/Trace and see

Madhu

|||You can check the last modified date of the phyiscal files. Im not sure abt this..
|||

Hey

Thanks for your responses. As suggested, I am using Profiler to watch the db for a period of time.

Cheers

Alec

|||When I face the problem you are facing, I change the status of the database to Offline, and then see if anybody notices. If nobody complains after a week/month or so, I remove the databases. If someone screams, it is then easy to identify who is using the database you think should not be in use.sql

Check for inactive database

Hi

I am working at a site that has no dedicated DBA, and our Sql Server 2000 environment has recently been playing up. There are several databases on the Production server that should be inactive (for applications no longer used).

What is the best way for me to see if there has been any recent activity on these databases (short of taking them offline and waiting for screams). Please keep in mind that I am a Sql Server novice.

Cheers

AFAIK ....there is no simple method or query to find which database is used when. The best method is to run profiler/Trace and see

Madhu

|||You can check the last modified date of the phyiscal files. Im not sure abt this..
|||

Hey

Thanks for your responses. As suggested, I am using Profiler to watch the db for a period of time.

Cheers

Alec

|||When I face the problem you are facing, I change the status of the database to Offline, and then see if anybody notices. If nobody complains after a week/month or so, I remove the databases. If someone screams, it is then easy to identify who is using the database you think should not be in use.

Check for inactive database

Hi

I am working at a site that has no dedicated DBA, and our Sql Server 2000 environment has recently been playing up. There are several databases on the Production server that should be inactive (for applications no longer used).

What is the best way for me to see if there has been any recent activity on these databases (short of taking them offline and waiting for screams). Please keep in mind that I am a Sql Server novice.

Cheers

AFAIK ....there is no simple method or query to find which database is used when. The best method is to run profiler/Trace and see

Madhu

|||You can check the last modified date of the phyiscal files. Im not sure abt this..
|||

Hey

Thanks for your responses. As suggested, I am using Profiler to watch the db for a period of time.

Cheers

Alec

|||When I face the problem you are facing, I change the status of the database to Offline, and then see if anybody notices. If nobody complains after a week/month or so, I remove the databases. If someone screams, it is then easy to identify who is using the database you think should not be in use.

check diskspace UNC via T-SQL

Hello,
I have an EM Job script that backups up my databases using SQLsafe to a
target UNC.
Sometimes, the target server that will store the backups is off line or
it's disk is full.
I am wondering how I can check to see if the machine is up via the job
and secondly, check the disk space.
If either check fails, I would then check another machine machine.
Any ideas appreciated,
Thanks
Rob
SQL 2000 Server and Enterprise, Windows 2003
SQL 2005 Server and Etnerprise, Windows 2003
Target storage is a Windows 2003 and I connect via UNCYou can use a couple of extended procs to get what you want. I have a
stored proc that jumps through some hoops to give me that information.

First I call: EXEC master.dbo.xp_availablemedia

That returns a list of devices on the database server. I loop over the
results from that and do:

EXEC master..xp_cmdshell 'DIR /-C <drive>'

and I look for the line that has "bytes free" and parse that for the
number.

It's not terribly elegant or fancy, but it does the job. The SQL for
the stored proc is below if you're curious. I also reference a table
that I created in msdb to help me track growth over time. You can just
eliminate that part.

Hope it helps,
Teresa Masino

CREATE procedure sp_checkdbspace
AS
SET nocount ON

CREATE TABLE #DriveList (
namevarchar(20)null,
lowfreeintnull,
highfree intnull,
mediatype intnull
)

CREATE TABLE #DirList (
Drive varchar(20) null,
DirResults varchar(255) null
)

INSERT INTO #DriveList EXEC master.dbo.xp_availablemedia

DECLARE @.Drive varchar(20),
@.CMD varchar(255)

DECLARE mycursor CURSOR
FOR
SELECTname
FROM#DriveList
ORDER BY name

OPEN mycursor

FETCH mycursor INTO @.Drive

IF CURSOR_STATUS('variable', '@.mycursor') = 0
BEGIN
PRINT 'No such device'
CLOSE mycursor
DEALLOCATE mycursor
return
END

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.CMD = 'insert into #DirList (DirResults) EXEC
master..xp_cmdshell ''DIR /-C ' + @.Drive + ''''
EXEC (@.CMD)
UPDATE #DirList SET Drive = @.Drive WHERE Drive IS NULL
FETCH mycursor INTO @.Drive
END

CLOSE mycursor
DEALLOCATE mycursor

SELECTDBName, LogicalName, PhysicalName, MinSize = min(SizeMB),
MaxSize = max(SizeMB), MinDate = min(StatusDate), MaxDate =
Max(StatusDate), MaxSizeMB = max(MaxSizeMB)
INTO#SpaceList
FROMmsdb..DBSpaceHistory
GROUP BY DBName, LogicalName, PhysicalName
ORDER BY DBName, LogicalName, PhysicalName

SELECT*, BytesFree = convert(numeric(18,0),
rtrim(ltrim(substring(replace(DirResults, ' bytes free', ''), 26,
50))))
INTO#SpaceOnDisk
FROM#DirList
WHEREDirResults LIKE '%bytes free%'

SELECTDBName = convert(varchar(20), DBName),
PhysicalName = convert(varchar(60), PhysicalName),
MaxSize,
Growth = MaxSize - MinSize,
DiskMBFree = convert(numeric(10,3), BytesFree / 1048576),
GrowthPeriod = datediff(day, MinDate, MaxDate),
DaysLeft = convert(numeric(10,3), (BytesFree / 1048576) / CASE WHEN
(MaxSize - MinSize) <= 0 THEN 1 ELSE ((MaxSize - MinSize) /
datediff(day, MinDate, MaxDate)) END)
FROM#SpaceList, #SpaceOnDisk
WHEREUPPER(substring(PhysicalName, 1, 3)) = Drive

GO

Check database healthiness on a daily basis?

How do people usually do to make sure all the databases are in good status?
We have about 50 databases. I was intended to write 'dbcc checkdb' for each
one in a T-SQL script and have it run every day. Is there a better way to
do that?
Thanks in advance for any advices.
BingHi,
Yes, that will be a better option to check and confirm that your database is
good. DBCC CHECKDB will run for a long time if your database is
big. In that case probably you can do this activity weekly once during non
peak hours (weekends).
Along with this you can also run UPDATE STATISTICS daily on those tables
which have high DML access (Insert/ Update/ Delete).
Monthly once check the fragmentation of table using DBCC
SHOWCONTIG(Table_name), if fragmented you could DBCC REINDEX the table.
THis will remove the fragmentation and increase the performance.
Thanks
Hari
MCDBA
"bing" <bing@.discussions.microsoft.com> wrote in message
news:DDCE66EE-CFB0-4C9D-B215-AB7983D4021E@.microsoft.com...
> How do people usually do to make sure all the databases are in good
status?
> We have about 50 databases. I was intended to write 'dbcc checkdb' for
each one in a T-SQL script and have it run every day. Is there a better way
to do that?
> Thanks in advance for any advices.
> Bing|||Thanks so much for your instance response, Hari. Not just for this one. Yo
u have answered a lot of my questions I posted previously. They are all ver
y helpful. I really appreciate your knowledgement and your kindness of will
ing to help others.
I'll try what you suggested.
Bing
"Hari Prasad" wrote:

> Hi,
> Yes, that will be a better option to check and confirm that your database
is
> good. DBCC CHECKDB will run for a long time if your database is
> big. In that case probably you can do this activity weekly once during non
> peak hours (weekends).
> Along with this you can also run UPDATE STATISTICS daily on those tables
> which have high DML access (Insert/ Update/ Delete).
> Monthly once check the fragmentation of table using DBCC
> SHOWCONTIG(Table_name), if fragmented you could DBCC REINDEX the table.
> THis will remove the fragmentation and increase the performance.
> Thanks
> Hari
> MCDBA
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:DDCE66EE-CFB0-4C9D-B215-AB7983D4021E@.microsoft.com...
> status?
> each one in a T-SQL script and have it run every day. Is there a better w
ay
> to do that?
>
>|||Bing,
Something else to keep in mind if you have some large db's or ones that are
24 x 7. You can restore a full backup on another machine and run the DBCC's
there. If it is corrupted on the primary it will be corrupted there as well
and you don't have to disrupt anyone while doing the check.
Andrew J. Kelly SQL MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> Thanks so much for your instance response, Hari. Not just for this one.
You have answered a lot of my questions I posted previously. They are all
very helpful. I really appreciate your knowledgement and your kindness of
willing to help others.[vbcol=seagreen]
> I'll try what you suggested.
> Bing
> "Hari Prasad" wrote:
>
database is[vbcol=seagreen]
non[vbcol=seagreen]
for[vbcol=seagreen]
way[vbcol=seagreen]|||Good point. Thanks for the advice, Andrew.
Bing
"Andrew J. Kelly" wrote:

> Bing,
> Something else to keep in mind if you have some large db's or ones that ar
e
> 24 x 7. You can restore a full backup on another machine and run the DBCC
's
> there. If it is corrupted on the primary it will be corrupted there as we
ll
> and you don't have to disrupt anyone while doing the check.
> --
> Andrew J. Kelly SQL MVP
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> You have answered a lot of my questions I posted previously. They are all
> very helpful. I really appreciate your knowledgement and your kindness of
> willing to help others.
> database is
> non
> for
> way
>
>|||Hi Andrew - just curious... what would the reason be for moving this data to
another DB? Is it to save CPU cycles? Isn't CHECKDB non-disruptive since
it just takes page locks?
Thanks for your help.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:unMcp8YdEHA.228@.TK2MSFTNGP11.phx.gbl...
> Bing,
> Something else to keep in mind if you have some large db's or ones that
are
> 24 x 7. You can restore a full backup on another machine and run the
DBCC's
> there. If it is corrupted on the primary it will be corrupted there as
well
> and you don't have to disrupt anyone while doing the check.
> --
> Andrew J. Kelly SQL MVP
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> You have answered a lot of my questions I posted previously. They are all
> very helpful. I really appreciate your knowledgement and your kindness of
> willing to help others.
> database is
> non
tables[vbcol=seagreen]
table.[vbcol=seagreen]
> for
better[vbcol=seagreen]
> way
>|||Besides the little bit of blocking CHECKDB is very resource intensive. It
can use a lot of CPU and potentially a lot of I/O. So if you have a large DB
to check you can hinder performance of other users if the load is great
enough. If you had a 500GB db and wanted to run CHECKDB it could take a
long time and potentially affect lots of users since it would most likely
run outside of a typical maintenance window. By running it on a nother
machine you might not care how long it takes and how much CPU, I/O etc it
takes. And you could specify the TABLOCK option to make it faster and more
comprehensive than on the production system. This is not something that
everyone needs to do but just wanted to mention it is an option for those
that need it.
Andrew J. Kelly SQL MVP
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23yqn58idEHA.244@.TK2MSFTNGP12.phx.gbl...
> Hi Andrew - just curious... what would the reason be for moving this data
to
> another DB? Is it to save CPU cycles? Isn't CHECKDB non-disruptive since
> it just takes page locks?
> Thanks for your help.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:unMcp8YdEHA.228@.TK2MSFTNGP11.phx.gbl...
> are
> DBCC's
> well
one.[vbcol=seagreen]
all[vbcol=seagreen]
of[vbcol=seagreen]
during[vbcol=seagreen]
> tables
> table.
good[vbcol=seagreen]
checkdb'[vbcol=seagreen]
> better
>

Check database healthiness on a daily basis?

How do people usually do to make sure all the databases are in good status?
We have about 50 databases. I was intended to write 'dbcc checkdb' for each one in a T-SQL script and have it run every day. Is there a better way to do that?
Thanks in advance for any advices.
Bing
Hi,
Yes, that will be a better option to check and confirm that your database is
good. DBCC CHECKDB will run for a long time if your database is
big. In that case probably you can do this activity weekly once during non
peak hours (weekends).
Along with this you can also run UPDATE STATISTICS daily on those tables
which have high DML access (Insert/ Update/ Delete).
Monthly once check the fragmentation of table using DBCC
SHOWCONTIG(Table_name), if fragmented you could DBCC REINDEX the table.
THis will remove the fragmentation and increase the performance.
Thanks
Hari
MCDBA
"bing" <bing@.discussions.microsoft.com> wrote in message
news:DDCE66EE-CFB0-4C9D-B215-AB7983D4021E@.microsoft.com...
> How do people usually do to make sure all the databases are in good
status?
> We have about 50 databases. I was intended to write 'dbcc checkdb' for
each one in a T-SQL script and have it run every day. Is there a better way
to do that?
> Thanks in advance for any advices.
> Bing
|||Thanks so much for your instance response, Hari. Not just for this one. You have answered a lot of my questions I posted previously. They are all very helpful. I really appreciate your knowledgement and your kindness of willing to help others.
I'll try what you suggested.
Bing
"Hari Prasad" wrote:

> Hi,
> Yes, that will be a better option to check and confirm that your database is
> good. DBCC CHECKDB will run for a long time if your database is
> big. In that case probably you can do this activity weekly once during non
> peak hours (weekends).
> Along with this you can also run UPDATE STATISTICS daily on those tables
> which have high DML access (Insert/ Update/ Delete).
> Monthly once check the fragmentation of table using DBCC
> SHOWCONTIG(Table_name), if fragmented you could DBCC REINDEX the table.
> THis will remove the fragmentation and increase the performance.
> Thanks
> Hari
> MCDBA
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:DDCE66EE-CFB0-4C9D-B215-AB7983D4021E@.microsoft.com...
> status?
> each one in a T-SQL script and have it run every day. Is there a better way
> to do that?
>
>
|||Bing,
Something else to keep in mind if you have some large db's or ones that are
24 x 7. You can restore a full backup on another machine and run the DBCC's
there. If it is corrupted on the primary it will be corrupted there as well
and you don't have to disrupt anyone while doing the check.
Andrew J. Kelly SQL MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> Thanks so much for your instance response, Hari. Not just for this one.
You have answered a lot of my questions I posted previously. They are all
very helpful. I really appreciate your knowledgement and your kindness of
willing to help others.[vbcol=seagreen]
> I'll try what you suggested.
> Bing
> "Hari Prasad" wrote:
database is[vbcol=seagreen]
non[vbcol=seagreen]
for[vbcol=seagreen]
way[vbcol=seagreen]
|||Good point. Thanks for the advice, Andrew.
Bing
"Andrew J. Kelly" wrote:

> Bing,
> Something else to keep in mind if you have some large db's or ones that are
> 24 x 7. You can restore a full backup on another machine and run the DBCC's
> there. If it is corrupted on the primary it will be corrupted there as well
> and you don't have to disrupt anyone while doing the check.
> --
> Andrew J. Kelly SQL MVP
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> You have answered a lot of my questions I posted previously. They are all
> very helpful. I really appreciate your knowledgement and your kindness of
> willing to help others.
> database is
> non
> for
> way
>
>
|||Hi Andrew - just curious... what would the reason be for moving this data to
another DB? Is it to save CPU cycles? Isn't CHECKDB non-disruptive since
it just takes page locks?
Thanks for your help.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:unMcp8YdEHA.228@.TK2MSFTNGP11.phx.gbl...
> Bing,
> Something else to keep in mind if you have some large db's or ones that
are
> 24 x 7. You can restore a full backup on another machine and run the
DBCC's
> there. If it is corrupted on the primary it will be corrupted there as
well[vbcol=seagreen]
> and you don't have to disrupt anyone while doing the check.
> --
> Andrew J. Kelly SQL MVP
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> You have answered a lot of my questions I posted previously. They are all
> very helpful. I really appreciate your knowledgement and your kindness of
> willing to help others.
> database is
> non
tables[vbcol=seagreen]
table.[vbcol=seagreen]
> for
better
> way
>
|||Besides the little bit of blocking CHECKDB is very resource intensive. It
can use a lot of CPU and potentially a lot of I/O. So if you have a large DB
to check you can hinder performance of other users if the load is great
enough. If you had a 500GB db and wanted to run CHECKDB it could take a
long time and potentially affect lots of users since it would most likely
run outside of a typical maintenance window. By running it on a nother
machine you might not care how long it takes and how much CPU, I/O etc it
takes. And you could specify the TABLOCK option to make it faster and more
comprehensive than on the production system. This is not something that
everyone needs to do but just wanted to mention it is an option for those
that need it.
Andrew J. Kelly SQL MVP
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23yqn58idEHA.244@.TK2MSFTNGP12.phx.gbl...
> Hi Andrew - just curious... what would the reason be for moving this data
to[vbcol=seagreen]
> another DB? Is it to save CPU cycles? Isn't CHECKDB non-disruptive since
> it just takes page locks?
> Thanks for your help.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:unMcp8YdEHA.228@.TK2MSFTNGP11.phx.gbl...
> are
> DBCC's
> well
one.[vbcol=seagreen]
all[vbcol=seagreen]
of[vbcol=seagreen]
during[vbcol=seagreen]
> tables
> table.
good[vbcol=seagreen]
checkdb'
> better
>

Check all databases for file space used

Hi There

As part of monitoring i want to hourly check the data file percentage used for each database, to monitor growth and know in advance when a data file will be full.

However i do not want to write a job for ever single database , this instance may have up to 100 databases that = 100 jobs.

So i want to write a job that checks the percentage space used for all databases.

My first dilema is that i cannot loop through databases dynamically, by that i mean if i use a cursor that loops through database names, and i dynamically build sql the say 'USE @.DBNAME' and execute it the cursor context stays local you do not actually change database context.

So how do i loop though databases, i have found

sp_msforeachdb, however this is undocumented in BOL.

Secondly how do i check the percentage of space used for the data file or files for a database, i could use DBCC SHOWFILESTATS, however this is also not documented in BOL.

Obviously i would rather use documented methods.

So bottom line what tsql could i use to check the percentage of file space use for all databases?

Thanx

In sql server 2005 you can do this:

select * from master.dbo.sysdatabases

returns the list of databases.

for each database you can do this:

select * from [database_name].dbo.sysfiles

This gives you the list of files used by the database. It has a "size" field that represents the number of 8k pages in use- so the total size in bytes of the database would be:

select sum ( size ) * 8192 from [database_name].dbo.sysfiles

The downside is that this is not documented and not recommended by microsoft as the database names may change.

I'm not sure if you can do this in sql2000 - you'd have to try.

|||

If this is sql server 2005 then you can use something like

select name , (size/max_size) [PERCENT] from sys.master_files

to get the file space used percentage for all the database files. You need to special case for some specific values of max_size like (0,-1). Look up the documentation for sys.master_files at http://msdn2.microsoft.com/en-us/library/ms186782.aspx for sql server 2005. Let me know if that works for you.Let me know if that works for you.

|||

Here you go; try this...

dbcc sqlperf(logspace)

|||

HI Guys

Ok i have tried you rsuggestions but this does not work, neither solution works because ia m interested in % file space used. In my case maxsize in both tables is -1 because they have unrestricted file growth, therefore i cannot calculate percentage used.

I want to monitor the file percentage used so that i can grow filegroups at specified times i do not want data files to auto grow during production hours. I would imagine many DBA's would want to monitor this, but how ? Like i said DBCC showfilestats works perfectly but is is undocumented? I also dont know how it works since sys.master_files and [database].dbo.sysfiles do not have an accurate maxsize(-1) so i cannot calculate it ?

Anyone?

Check all databases for file space used

Hi There

As part of monitoring i want to hourly check the data file percentage used for each database, to monitor growth and know in advance when a data file will be full.

However i do not want to write a job for ever single database , this instance may have up to 100 databases that = 100 jobs.

So i want to write a job that checks the percentage space used for all databases.

My first dilema is that i cannot loop through databases dynamically, by that i mean if i use a cursor that loops through database names, and i dynamically build sql the say 'USE @.DBNAME' and execute it the cursor context stays local you do not actually change database context.

So how do i loop though databases, i have found

sp_msforeachdb, however this is undocumented in BOL.

Secondly how do i check the percentage of space used for the data file or files for a database, i could use DBCC SHOWFILESTATS, however this is also not documented in BOL.

Obviously i would rather use documented methods.

So bottom line what tsql could i use to check the percentage of file space use for all databases?

Thanx

In sql server 2005 you can do this:

select*from master.dbo.sysdatabases

returns the list of databases.

for each database you can do this:

select*from [database_name].dbo.sysfiles

This gives you the list of files used by the database. It has a "size" field that represents the number of 8k pages in use- so the total size in bytes of the database would be:

select sum ( size ) * 8192from [database_name].dbo.sysfiles

The downside is that this is not documented and not recommended by microsoft as the database names may change.

I'm not sure if you can do this in sql2000 - you'd have to try.

|||

If this is sql server 2005 then you can use something like

select name , (size/max_size) [PERCENT] from sys.master_files

to get the file space used percentage for all the database files. You need to special case for some specific values of max_size like (0,-1). Look up the documentation for sys.master_files at http://msdn2.microsoft.com/en-us/library/ms186782.aspx for sql server 2005. Let me know if that works for you.Let me know if that works for you.

|||

Here you go; try this...

dbcc sqlperf(logspace)

|||

HI Guys

Ok i have tried you rsuggestions but this does not work, neither solution works because ia m interested in % file space used. In my case maxsize in both tables is -1 because they have unrestricted file growth, therefore i cannot calculate percentage used.

I want to monitor the file percentage used so that i can grow filegroups at specified times i do not want data files to auto grow during production hours. I would imagine many DBA's would want to monitor this, but how ? Like i said DBCC showfilestats works perfectly but is is undocumented? I also dont know how it works since sys.master_files and [database].dbo.sysfiles do not have an accurate maxsize(-1) so i cannot calculate it ?

Anyone?

character types

i am using sql server 2000, i am new to databases, could you please tell me what are the different character types, example "varchar , numeric...etc"
How to find out the difference between them...
Where do i find a tutorial for this kind of basic knowledge ?
regardsHomework questions are not permitted on these forums, and ones that are written so brazenly will just invite ridicule at the poster.

Have a good day now.

Regards,|||A great resource for this sort of things is our best friend Google...
Search for "T-SQL data types" and you'll get a lovely handful of links that will tell you exactly what you want to know! :)

Once you've had a read, if you any specific questions post them back here!|||Oh George, you really are far too tolerant.|||That's because I don't think this one is homework (yet) ;)|||homework is indeed allowed and is often some of the more interesting posts.|||homework is indeed allowed and is often some of the more interesting posts.

I'll get my coat.|||Hi there,

Check the SQL server 2000 help file (called books online or BOL), if you do a search on 'data types' you should find everything you need.|||I'll get my coat.
Well, it is allowed, but it is certainly treated differently.
We do our best to help the poster to the answer themselves, but if they're unwilling then so are we.

I can understand why you think this one might be homework - but I personally don't see it yet. If you asked me a year ago what the datatypes were, I could probably name 3 :p
Not to mention I'd have even less of an idea what "BoL" stands for ;)|||The ignorance of youth.

Once you get to my age (24) you tend to show less emotion and occasionally affront a manner of indifference, often unknowingly, when you fine yourself in a social predicament. Such causes of this decline to a dour emotional state vary widely, though one that invariably seems to be effective, and which is notably the most prominent, is when one is asked to answer a painfully trivial question. The immediate reaction is to swiftly determine whether the original post was said in jest, an attempted cure to the illness described above, or if in fact, it was a question. Occasionally in exercising this judgment, we may reach the wrong conclusion.|||Occasionally in exercising this judgment, we may reach the wrong conclusion.Been there, done that, got the blood-stained tee-shirts to prove it! No serious harm done, such is life.

Twenty four years old... Yikes, I remember that wistfully!

-PatP|||Twenty four years old... Yikes, I remember that wistfully!
-PatP

I'll let you know my perspective in the year 2047.|||I'll let you know my perspective in the year 2047.Assuming that I'm still alive at that point, I'll be eagerly awaiting your analysis! ;)

-PatP|||Once you get to my age (24)damn...people around you are gonna be in BIG trouble then, by the time you get to my age (47)|||I thought I was ahead of my age in cynicism, but I think Robert may have taken the gold for that ;)

kc3377, how are you doing with your original question, do you have all the answers you need?

char vs. varchar

Greetings,

I have a question. I work on some SQL2k/ASP.NET apps at work. My
predacessor, who created the databases/tables seemed to have liked to
use 'char' for all text fields. Is there a reason why he would have
done this over using varchar? It's a minor annoyance to always have to
RTRIM data and it makes directly making changes to the database more
annoying (with all the pointless trailing spaces)?

I usually use char for fixed string lengths, like state abbreviations
or something, and varchar for strings of unknown length.

Is it a performance issue? Our database doesn't do much traffic, for
the most part.It's not a performance issue unless you're using varchar(1) and the
overhead that incurs and have millions of records and higher traffic
than you probably have.

As a matter of fact, for larger char() fields, they can be slower than
varchar(), because it has to physically store more data pages than if
you used varchar(). If there are more data pages for the same number
of records, things get slower.

Make life easy on yourself and use varchar(). Don't use varchar(1)
though. I have seen people use it.|||The difference between char and varchar are in both storage and performance:

1. Storage wise: char columns have fixed length. If the user supplied value
for the column is less than the fixed length defined in the schema, the
column is padded with 0 at end to make the total length fixed. varchar
doesn't have a fixed length thus no padding is needed. But as the result
varchar columns have to store the size of the data together with the column
data, which takes an extra 2 bytes per varchar column.

2. Performance wise locating char is a little faster than varchar. Since
char columns have fixed length, they are stored in fixed location in a row.
This means locating a char column can directly jump to the fixed location in
a row to read. For varchar column since the size of the data is variable,
they can't be stored in fixed location in a row and rather there is soem
kind of lookup table in the row format to store the location of each varchar
column. This means locating a varchar column has to lookup the location of
the column in the lookup table stored in the row first before jumping to the
location to read. Referencing the lokup table introduces some perofrmance
overhead, especially ifthe lookup table reference causes cache line miss.

In summary, it is a matter of trade-off between padding+faster locate and
2-bytes-overhead-per-column+slower locate when choosing char v.s. varchar.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
<dmhendricks@.despammed.com> wrote in message
news:1105723409.312275.186390@.f14g2000cwb.googlegr oups.com...
> Greetings,
> I have a question. I work on some SQL2k/ASP.NET apps at work. My
> predacessor, who created the databases/tables seemed to have liked to
> use 'char' for all text fields. Is there a reason why he would have
> done this over using varchar? It's a minor annoyance to always have to
> RTRIM data and it makes directly making changes to the database more
> annoying (with all the pointless trailing spaces)?
> I usually use char for fixed string lengths, like state abbreviations
> or something, and varchar for strings of unknown length.
> Is it a performance issue? Our database doesn't do much traffic, for
> the most part.|||You would never see a practical performance advantage in using char
over varchar, unless you had an extremely high transaction application.
The varchar offset lookup is optimized in-memory. The real bottleneck
is disk I/O, not a few extra CPU cycles from looking up varchar
offsets.

If I have an app that uses a char(80), versus an app that uses a
varchar(80), with an average width of data of 40, the char(80) data is
going to use approximately twice as many data pages to store the data.
That means twice as much disk I/O to read the table, which is where the
real bottleneck is.

I don't believe there is any tradeoff here.|||Gary, see inline

Gary wrote:
> You would never see a practical performance advantage in using char
> over varchar, unless you had an extremely high transaction application.
> The varchar offset lookup is optimized in-memory.

I agree that you won't see any performance degradation here.

> The real bottleneck is disk I/O, not a few extra CPU cycles from looking up varchar
> offsets.

Again, I agree

> If I have an app that uses a char(80), versus an app that uses a
> varchar(80), with an average width of data of 40, the char(80) data is
> going to use approximately twice as many data pages to store the data.
> That means twice as much disk I/O to read the table, which is where the
> real bottleneck is.

This is only true in a perfect world scenario. If there is insufficient
free space to accomodate changes in the varchar data, then change
changes in the varchar(80) data will lead to fragmentation. Changes in
the char(80) data will not lead to fragementation, because any
replacement can be done in-place (assuming columns not part of a
clustered index).

So depending on the fill-factor, number of data changes, etc.
fragmentation will be a little or much greater for varchar compared to
char. This fragmentation is (as you probably know) especially expensive,
because it needs random I/O which is slower than sequential I/O.

I you reindex regularly, and have a sufficient fill factor, then
varchar(80) should always perform better if the average length is only
40.

> I don't believe there is any tradeoff here.

Personally, I don't see a good reason why one would ever choose a
varchar over char when the maximum size is 4 characters or less. For
sizes over 10 characters I tend to choose varchar almost automatically.
For anything between 4 and 10 I really think about the situation before
deciding char or varchar.

Gert-Jan|||Gert-Jan -

I totally agree with you. I simplified the situation quite a bit, but
with all other things being equal, yours is a good "guesstimate".

I ran a test "perfect world" scenario of char(80) vs. varchar(80) (40
char avg len), and both the CPU time and disk I/O were about 40% higher
with the char(80) scenario with 10000 records.

Gary

Chaning password

I have several merge replications running pushing subscriptions from one
server to the other. After some problems we recreated the databases (se
previous questions). However after pushing up all data everything worked fine
until we changed the administrator password on the subsciber server. Now I
get an error message 20084 at the publisher /18452 at the subscriber and the
message "Login failed for user '(null)'. Reason: Not associated with a
trusted SQL Server connection." What is the problem? i have registred the
servers via the 'sa' user and connect via the 'sa' user and have both
sql-server and windows as login possibilities. Sql-server and agent is
started via the administrator account (win2000) but i have stopped and
restarted the service changing the password in the services.
Best wishes
Mats
The "Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection." error normally means one of three things.
The NT account you are using to authenticate with does not have rights to
access the machine, its password is invalid, or you are using windows
authentication only and trying to connect using a SQL account and password.
You have to be a little careful with the account on the subscriber.
When the subscriber's SQL Server agent connects to the publisher it by
default will impersonate the SQL Server agent account on the publisher. In
other words the accounts and passwords of the account the SQL Server agent
on the Subscriber will have to match the account on the Publisher, or will
have to be in the administrator group on the publisher.
You can also specify a SQL account. In this case your SQL Server agent
account on the subscriber will have to match the SQL Server agent account on
the publisher, or be in the administrator group, or have rights to access
the snapshot folder share on the publisher.
I think your best approach, if you can do it, is to make the administrator
accounts on both servers have the same password, or to use another
administrator account on the subscriber, and add this account to the admin
group on the publisher.
HTH - its a little tricky to implement.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Mats" <Mats@.discussions.microsoft.com> wrote in message
news:E2956017-73FA-4EBA-8D86-F58A0B4D24C7@.microsoft.com...
>I have several merge replications running pushing subscriptions from one
> server to the other. After some problems we recreated the databases (se
> previous questions). However after pushing up all data everything worked
> fine
> until we changed the administrator password on the subsciber server. Now I
> get an error message 20084 at the publisher /18452 at the subscriber and
> the
> message "Login failed for user '(null)'. Reason: Not associated with a
> trusted SQL Server connection." What is the problem? i have registred the
> servers via the 'sa' user and connect via the 'sa' user and have both
> sql-server and windows as login possibilities. Sql-server and agent is
> started via the administrator account (win2000) but i have stopped and
> restarted the service changing the password in the services.
>
> --
> Best wishes
> Mats