Few questions on Backups for warehouse

I am starting to load a data warehouse for a retention period of 10 years. My database backup plan is as follows -

1. Perform full back on Sunday.
2. Perform differential backup everyday from Mon - Sat.
3. Perform transaction log back-ups every hour on all days.

My recovery mode is going to be BULK-LOGGED at all times. I had a few questions / comments on the Maintenance Plan that I would be creating for the back-ups. My database name is Warehouse.

1. Differential Backups cannot be created via a Maintenance Plan. Only a full-backup gets created. Am I correct?

2. I shall be running Optimizations and Integrity checks prior to full-backup. Is this ok?

3. Remove files (both .BAK and .TRN) older than - I am thinking of having 6 days. I want only one full back-up at a time in the server. What settings can I use? I think the old back-up gets deleted when the new one is successful. What settings in the Maintainence Plan do I have to use to overwrite the previous back-up with the current one?

System Databases -

Should the settings for System Databases be the same as my Warehouse database?

The Maintenance Plan takes care of full-back up and TLOG back-ups. For Differential Backups I have to use the All Tasks from EM and specify the Differential Backup job. Correct?

All kinds of back-ups can occur in the database when it is active. Meaning, I have a job that loads data in the warehouse when a back-up is occurring simultaneously. Am I correct?

I do not intend to shrink the Transaction Log at any time, since it gets backed up every hour I do not expect it to grow to a large size. If I do have to shrink it, then I change the recover mode to Simple, shrink the log and then immediately do a full-backup and after that set the mode back to Bulk-Logged. Is the sequence of steps correct?

Please let me know your inputs.

Thanks,

Vivek1. Incorrect. Please refer to BOL.

2. Sure it's okay. It can be resource intensive so you may consider running during non-peak hours. Also, you should always run DBCC CHECKDB before a backup and if possible, after a backup completes. the sooner you find a problem, the better.

3. Delete all tran and diff backups after a successful full backup completes and has been written to another media (secondary copy) such as tape

IF YOU ARE RUNNING TRAN LOG BACKUPS, YOUR TRAN LOG GETS AUTOMATICALLY TRUNCATED. IN ANY CASE, YOU ARE RUNNING IN BULK MODEL. YOU REALLY SHOULD READ BOL.

Fastest way to move XML document into and back out of CLR function

Fastest way to move XML document into and back out of CLR function
In SQL Server 2005 and Visual Studio 2005, what is the fastest way to pass
an xml data type variable into a C# CLR function and retrieve it back out of
the C# CLR function when the C# CLR function is done modifying it?
I tried this but get deployment error becuase SQLServer2005/CLR dotn support
System.Xml.XmlDocument:
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static int InsertSomeNode(System.Xml.XmlDocument pDoc)
{
//
return 1;
}
};
thorws error:
Error 1 Column, parameter, or variable #1: Cannot find data type
XmlDocument. InsertSomeNodeHi
the SQL XML datatype is a SqlXml type in the System.Data.SqlTypes namespace.
There is no .NET equivalent see
http://msdn2.microsoft.com/en-us/library/ms131092.aspx
John
"DR" wrote:
> Fastest way to move XML document into and back out of CLR function
> In SQL Server 2005 and Visual Studio 2005, what is the fastest way to pass
> an xml data type variable into a C# CLR function and retrieve it back out of
> the C# CLR function when the C# CLR function is done modifying it?
> I tried this but get deployment error becuase SQLServer2005/CLR dotn support
> System.Xml.XmlDocument:
> public partial class UserDefinedFunctions
> {
> [Microsoft.SqlServer.Server.SqlFunction]
> public static int InsertSomeNode(System.Xml.XmlDocument pDoc)
> {
> //
> return 1;
> }
> };
> thorws error:
> Error 1 Column, parameter, or variable #1: Cannot find data type
> XmlDocument. InsertSomeNode
>
>

fast kill/rollback

I have just killed a transaction and it says it's going to take about 20
hours to roll the change back. Is there such a thing as a "fast kill"? I
don't care about rolling this back - I just want it to stop. The process is
blocking my users and I need this process killed sooner than 20 hours from
now. I also don't understand how/why a process that had run for 9 hours
could take 20 hours to rollback. It doesn't really make sense to me.
Thanks in advance.
AndreHi
Generally, if a transaction took 9 hours, you can expect a rollback to take
12-24 hours (1.5-3 times the forward time).
It has to read the original row from the transaction log, and apply it back
to the database. This is very IO intensive. I hope your transaction log is
on a separate physical drive/LUN.
It does not help to stop the SQL Server service as on startup, it will do
the rollback and it will take the same time.
You should never have very long transactions, as it can take a long time to
rollback as anything can happen over such a long period (power, network
server problems)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>|||> Is there such a thing as a "fast kill"? I don't care about rolling this
> back - I just want it to stop.
The only way to avoid the rollback is to restore from backup.
--
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>|||bummer.
In that case, maybe I should ask for advice on how to do this. I'm trying
to add a new column to a table that has 9 cols and 34 million records. I'm
trying to add an identity col called RowID, using alter table. Is there a
better way to do this? Should I drop all indexes before running this
command?
Thanks, Andre|||Even on the worst hardware I can think of this should not take 9 hours to
do. Most likely you were blocked and it wasn't doing anything. But in any
case you might want to BCP out the data, create a new table just the way you
need with no indexes. BCP the data back in (you will probably need a format
file since the structure will be different) and recreate the indexes. That
way you can get a minimally logged load if you do the BCP or Bulk Insert
properly.
--
Andrew J. Kelly SQL MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>|||Another method besides the BCP method Andrew suggested is to create a new
table using SELECT INTO, creating the new column using the IDENTITY
function. This will also be minimally logged in the SIMPLE recovery model.
You can then drop the old table and recreate constraints and indexes.
--
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>|||I thought about that one too but wondered about the logging. I'll give that
a try tonight and see how it goes.
I also do think there was some blocking going on. I'm going to stop SQL
Agent tonight before starting.
Thanks again,
Andre
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OO%23OJIYDGHA.3992@.TK2MSFTNGP12.phx.gbl...
> Another method besides the BCP method Andrew suggested is to create a new
> table using SELECT INTO, creating the new column using the IDENTITY
> function. This will also be minimally logged in the SIMPLE recovery
> model. You can then drop the old table and recreate constraints and
> indexes.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Andre" <no@.spam.com> wrote in message
> news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
>> bummer.
>> In that case, maybe I should ask for advice on how to do this. I'm
>> trying to add a new column to a table that has 9 cols and 34 million
>> records. I'm trying to add an identity col called RowID, using alter
>> table. Is there a better way to do this? Should I drop all indexes
>> before running this command?
>> Thanks, Andre
>

fast kill/rollback

I have just killed a transaction and it says it's going to take about 20
hours to roll the change back. Is there such a thing as a "fast kill"? I
don't care about rolling this back - I just want it to stop. The process is
blocking my users and I need this process killed sooner than 20 hours from
now. I also don't understand how/why a process that had run for 9 hours
could take 20 hours to rollback. It doesn't really make sense to me.
Thanks in advance.
Andre
> Is there such a thing as a "fast kill"? I don't care about rolling this
> back - I just want it to stop.
The only way to avoid the rollback is to restore from backup.
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>
|||bummer.
In that case, maybe I should ask for advice on how to do this. I'm trying
to add a new column to a table that has 9 cols and 34 million records. I'm
trying to add an identity col called RowID, using alter table. Is there a
better way to do this? Should I drop all indexes before running this
command?
Thanks, Andre
|||Even on the worst hardware I can think of this should not take 9 hours to
do. Most likely you were blocked and it wasn't doing anything. But in any
case you might want to BCP out the data, create a new table just the way you
need with no indexes. BCP the data back in (you will probably need a format
file since the structure will be different) and recreate the indexes. That
way you can get a minimally logged load if you do the BCP or Bulk Insert
properly.
Andrew J. Kelly SQL MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>
|||Another method besides the BCP method Andrew suggested is to create a new
table using SELECT INTO, creating the new column using the IDENTITY
function. This will also be minimally logged in the SIMPLE recovery model.
You can then drop the old table and recreate constraints and indexes.
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>
|||I thought about that one too but wondered about the logging. I'll give that
a try tonight and see how it goes.
I also do think there was some blocking going on. I'm going to stop SQL
Agent tonight before starting.
Thanks again,
Andre
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OO%23OJIYDGHA.3992@.TK2MSFTNGP12.phx.gbl...
> Another method besides the BCP method Andrew suggested is to create a new
> table using SELECT INTO, creating the new column using the IDENTITY
> function. This will also be minimally logged in the SIMPLE recovery
> model. You can then drop the old table and recreate constraints and
> indexes.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Andre" <no@.spam.com> wrote in message
> news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
>
|||Hi
Generally, if a transaction took 9 hours, you can expect a rollback to take
12-24 hours (1.5-3 times the forward time).
It has to read the original row from the transaction log, and apply it back
to the database. This is very IO intensive. I hope your transaction log is
on a separate physical drive/LUN.
It does not help to stop the SQL Server service as on startup, it will do
the rollback and it will take the same time.
You should never have very long transactions, as it can take a long time to
rollback as anything can happen over such a long period (power, network
server problems)
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>

fast kill/rollback

I have just killed a transaction and it says it's going to take about 20
hours to roll the change back. Is there such a thing as a "fast kill"? I
don't care about rolling this back - I just want it to stop. The process is
blocking my users and I need this process killed sooner than 20 hours from
now. I also don't understand how/why a process that had run for 9 hours
could take 20 hours to rollback. It doesn't really make sense to me.
Thanks in advance.
AndreHi
Generally, if a transaction took 9 hours, you can expect a rollback to take
12-24 hours (1.5-3 times the forward time).
It has to read the original row from the transaction log, and apply it back
to the database. This is very IO intensive. I hope your transaction log is
on a separate physical drive/LUN.
It does not help to stop the SQL Server service as on startup, it will do
the rollback and it will take the same time.
You should never have very long transactions, as it can take a long time to
rollback as anything can happen over such a long period (power, network
server problems)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>|||> Is there such a thing as a "fast kill"? I don't care about rolling this
> back - I just want it to stop.
The only way to avoid the rollback is to restore from backup.
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>|||bummer.
In that case, maybe I should ask for advice on how to do this. I'm trying
to add a new column to a table that has 9 cols and 34 million records. I'm
trying to add an identity col called RowID, using alter table. Is there a
better way to do this? Should I drop all indexes before running this
command?
Thanks, Andre|||Even on the worst hardware I can think of this should not take 9 hours to
do. Most likely you were blocked and it wasn't doing anything. But in any
case you might want to BCP out the data, create a new table just the way you
need with no indexes. BCP the data back in (you will probably need a format
file since the structure will be different) and recreate the indexes. That
way you can get a minimally logged load if you do the BCP or Bulk Insert
properly.
Andrew J. Kelly SQL MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>|||Another method besides the BCP method Andrew suggested is to create a new
table using SELECT INTO, creating the new column using the IDENTITY
function. This will also be minimally logged in the SIMPLE recovery model.
You can then drop the old table and recreate constraints and indexes.
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>|||I thought about that one too but wondered about the logging. I'll give that
a try tonight and see how it goes.
I also do think there was some blocking going on. I'm going to stop SQL
Agent tonight before starting.
Thanks again,
Andre
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OO%23OJIYDGHA.3992@.TK2MSFTNGP12.phx.gbl...
> Another method besides the BCP method Andrew suggested is to create a new
> table using SELECT INTO, creating the new column using the IDENTITY
> function. This will also be minimally logged in the SIMPLE recovery
> model. You can then drop the old table and recreate constraints and
> indexes.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Andre" <no@.spam.com> wrote in message
> news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
>